Optimizing a SQL join statement

Question

I am joining two tables. The first contains work orders and their associated part numbers. The second contains the BOM for all of the part numbers. They are both large tables. Individually, I can query the two tables in seconds if not less. When I perform the join, it takes minutes. Is it possible that the where at the end of this statement is being performed after the join? If the join is performed first, I could see this taking a long time. But if the first table is reduced first by the where, I would think this should go fast. Is there someway to write a more optimized query?

SELECT  Table2.ItemNum As ItemNum  
FROM Table1  
INNER Join Table2  
ON Table1.PartNum = Table2.PartNum
WHERE Table1.WorkOrder = 10100314

Have you indexed your foreign key columns? What does the execution plan look like? What RDBMS is this for? — Martin Smith
– Martin Smith, Commented Apr 13, 2011 at 23:08
Do you have an index on Table1.WorkOrder? On the PartNum fields? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 13, 2011 at 23:19
what you need is to index your tables. What database engine are you using? — mariocase84
– mariocase84, Commented Apr 13, 2011 at 23:21

Akram Shahda · Accepted Answer · 2011-04-13 23:19:16Z

1

That will do a better job:

SELECT  Table2.ItemNum As ItemNum  
FROM Table2  
INNER JOIN
(
    SELECT * 
    FROM Table1
    WHERE Table1.WorkOrder = 10100314
)AS Table1
ON Table1.PartNum = Table2.PartNum

Indexes on PartNum fields are required too ...

answered Apr 13, 2011 at 23:19

Akram Shahda

14.7k5 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mcolson1590 Over a year ago

Thanks, it helped a bit, but still slow. I'll have to see if they are indexed or if there is an indexed field that I should be using.

mcolson1590 Over a year ago

Used a different field and amazing speed. Thanks

Akram Shahda Over a year ago

@Ken @mcolson1590: I am not aware of the difference between the two answers here ..

Ken White Over a year ago

You apparently are a little faster typist. :) Deleting my answer, as yours was first.

Collectives™ on Stack Overflow

Optimizing a SQL join statement

1 Answer 1

That will do a better job:

Indexes on PartNum fields are required too ...

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

That will do a better job:

Indexes on PartNum fields are required too ...

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related