May 21, 2003 at 12:29 pm
I have a query that joins two tables on a part number and returns all parts that exist in table1 that are cheaper than table2. Its a little slow. Any help optimizing this would be appreciated.
SELECT TOP X
TABLE1.PART_NBR, TABLE2.PART_NBR, TABLE1.COST
FROM TABLE2 WITH (NOLOCK)
JOIN TABLE1 ON (TABLE1.PART_NBR = TABLE2.PART_NBR)
AND TABLE1.COST > 0
AND TABLE1.COST < .COST
AND TABLE2.MFG_NAME LIKE '#manuf#%'
Thanks,
Andrew
Andrew
http://eshopsoho.com
May 21, 2003 at 1:11 pm
Try the query without the NOLOCK hint, without the 4-part JOIN (use WHERE instead). If still slow, take out the LIKE expression and rerun. If you determine the LIKE expression is the culprit, take a look at indexing on MFG_NAME. Try this first:
SELECT TOP 100
TABLE1.PART_NBR
, TABLE1.COST
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.PART_NBR = TABLE2.PART_NBR
WHERE
TABLE1.COST > 0
AND TABLE1.COST < TABLE2.COST
AND TABLE2.MFG_NAME LIKE '#manuf#%'
May 21, 2003 at 1:13 pm
Also, it might be a heck of a lot more efficient if you were filtering on a MFG_ID, instead of a name. Is that possible, given your query requirements?
May 21, 2003 at 2:00 pm
I went ahead and changed that. Seems to yield the same results. Problem is I think is that I have a clustered index on a different column for other main queries so this mfg_part is not indexed in either table.
Anyhow, here are the results of the two queries.
Table1
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 15 ms.
Table2
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 64 ms.
I think I see the problem. I appreciate your input!
Andrew
Andrew
Andrew
http://eshopsoho.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply