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 |
|
lcpx
Yak Posting Veteran
54 Posts |
 Posted - 2006-07-17 : 11:18:51
|
I have a SQL which join 3 tables and normally take 30 seconds to run. Today we change one table slightly but the same SQL turn to run 40 minutes and now it’s still running. The columns I added is varchar(10) type on t1. At the moment the whole column completely null. Why it makes so big difference? Can anybody help me out. Thanks very much.SELECT t1.Period, t1.Week_No, t1.SKU_No, t2.RETAIL_PRICE, t3.Promotion_Description, t1.Sales_Vol, FROM dbo.F_SALES t1LEFT JOIN (SELECT * FROM dbo.F_RETAIL WHERE Retailer_Description = 'Dealer 1') t2ON t1.Week_No = t2.Week_NoAND t1.SKU_No = t2.SKU_NoLEFT JOIN dbo.D_RETAIL_PROMOTION t3ON t2.Promotion_ID = t3.Promotion_ID |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-17 : 18:09:56
|
You haven't got any transactions outstanding on one of these tables do you?? (try using WITH NOLOCK hint to see if it makes any difference - but don't leave it there unless you know what you're doing....)Also, you might want to try removing your Retail_Description filter to see if it makes any difference:SELECT t1.Period, t1.Week_No, t1.SKU_No, t2.RETAIL_PRICE, t3.Promotion_Description, t1.Sales_Vol, FROM dbo.F_SALES t1LEFT JOIN dbo.F_RETAIL t2ON t1.Week_No = t2.Week_NoAND t1.SKU_No = t2.SKU_NoAND t2.Retailer_Description = 'Dealer 1'LEFT JOIN dbo.D_RETAIL_PROMOTION t3ON t2.Promotion_ID = t3.Promotion_ID Otherwise, have a look at the execution plan to see what other bottlenecks are happening. HTH,Tim |
 |
|
|
lcpx
Yak Posting Veteran
54 Posts |
Posted - 2006-07-18 : 05:42:21
|
| Cheers Tim. Firstly I can make sure there is no lock problem, I've rebooted the server and it didn't help. I've sorted the problem by rebuilding the table, but what happened is still puzzling me.I created a new table with exactly the same table strucure as F_Sales. Then inserted all the data back to this table. (about 200,000 rows) I used the same sql but against the new table F_Sales. it only took 20 seconds to run. So I am thinking that problably in SQL server once we used the table for a while, we might need to do the data export and import and rebuild the indexes. Could anybody give some advice on it. Thanks very much indeed! |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-07-18 : 06:30:16
|
| Rebuild the indexes, update the stats-------Moo. :) |
|
|
|
|
|
|
|
|
Why the SQL suddenly runs so slow?