Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2009-01-15 : 11:03:14
|
| I have a query that was running relatively smoothly, returning roughly a million rows in about 10 seconds. I then added in 2 additional "inner joins" to 2 more tables and the time ballooned up to about 5 minutes. Are there any tips or little tricks I can do within my query to cut down run time?Also, these tables are locked down and out of my reach to edit so setting indexes or any physical editting of the tables themselves isn't an option.Here's a sample of my query. Nothing crazy, just a select statement with a bunch of joins. The bolded text in ALL CAPS are the new pieces that ballooned up my query:----------------------------------------------SELECT a.productID, a.product_description, b.qtyShipped, f.shipDate, H.ORDER_DATE, H.ORDER_TYPEFROM tableA a INNER JOIN tableB b ON b.productID = a.productID and isnull(b.productInactive,0) = 0 INNER JOIN tableC c ON c.sequenceID = b.sequenceID INNER JOIN tableD d ON d.shipID = c.shipID INNER JOIN tableE e ON e.orderID = d.orderID INNER JOIN tableF f ON f.verID = e.verID INNER JOIN TABLEG G ON G.SCANID = F.SCANID INNER JOIN TABLEH H ON H.PHOTOID = G.PHOTOIDWHERE f.shipDate >= '1/1/2008' AND f.shipDate < '1/1/2009'-------------------------------------------------------Any ideas as to how I can trim down my time? Would splitting these into 2 temp tables and then joining on the temp tables work, or something like that?Thanks.-Goalie35 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-01-15 : 11:13:15
|
| Do you have indexes on the columns G.SCANID , F.SCANID , H.PHOTOID , G.PHOTOID ? Did you look at the query plan before and after to see what changed?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-15 : 11:49:32
|
| If it is not possible to add indexes to the tables, and you feel they are required - you could create an indexed view using the particular columns you feel need indexed, then join to the indexed view. If you are not using Enterprise edition remember to use noexpand hint. |
|
|
|
ccorbin
Starting Member
1 Post |
Posted - 2009-01-18 : 02:08:24
|
| Adding the necessary indexes to support your joins is your best bet, however if you truely cant add indexes, here is a suggestion to cut down the run time (depending on your max DOP setting, you may or may not see an benefit from this... so if it doesnt work at first, check the setting. Oh yeah, you'd have to be on a multi-processor box too)Take the Date range in your where clause, and break it up into N intervals where N = the number of processors on the server. Then, code a seperate SELECT statement for each date range, and then merge the results using the UNION ALL (IMPORTANT! Do NOT use UNION, because if you do, its going to essentially do SELECT DISTINCTS to ensure it doesnt return any duplicates. Just UNION ALL, the nature of breaking the query up like this ensures no duplicates will exist among date ranges).You will allow the date ranges in your query to be retrieved in parallel, therefore you could see your run time come down. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-18 : 09:04:04
|
| Are TABLEH & TABLEG really big? Also what will be average % of records you would be interested in them?Also you would get an idea of performance bottlenecks if you can analyse the query's execution plan. |
|
|
|
|
|
|
|
|
Help! Query time went from 10 sec to 5 minutes!