December 11, 2002 at 1:00 pm
I have encountered something lately that I think may be a bug in one of the latest hotfixes ( or maybe not, since I haven't had the resources to try to duplicate the problem in an earlier release ) The reason I think it may be in the hotfix is that it hadn't seen the problem until after applying the latest hotfix back in october. ( I am now on 8.00.0679 )
What I am seeing is basically horrible performance on queries using complex IN clauses. For some reason queries that used to take less than a minute, now seem to run into hours. I have found that if I rewrite the query so that the SQL in the IN clause is pre-selected into a table variable, the performance returns to normal levels. It seems as though the optimizer is not realizing that it only needs to run the query in the in clause once, and instead is running it for each row.
Has anyone else seen this or have any thoughts?
Here are 2 sample queries:
/** The first one, used to run fine, now takes hours. *************/
Select OrderNumber, OpenDate, Amount, Amex.AmexNum, Type, Account.AccountID, SalesResp, SalesID
From Amex
join Compass.dbo.Account Account on left(OrderNumber,10) = right(MerchantNumber,10)
Join Profitability Prof on Prof.MerchantNum = Account.merchantNumber
and Prof.ReportDate = '11/30/02'
where Amount > 0
and Amex.ReportDate = '11/30/02'
and Account.AccountID in (
Select distinct Max(Account.AccountID) from Amex
join Compass.dbo.Account Account on left(OrderNumber,10) = right(MerchantNumber,10)
where Amount > 0
and Amex.ReportDate = '11/30/02'
Group by Amex.AmexNum
)
/***
Now here is the replacement query, which does the same thing, and runs in a few seconds
**/
declare @Tmp table ( AccountID int Primary Key)
insert into @Tmp
Select distinct Max(Account.AccountID) from Amex
join Compass.dbo.Account Account on left(OrderNumber,10) = right(MerchantNumber,10)
where Amount > 0
and Amex.ReportDate = '11/30/02'
Group by Amex.AmexNum
Select OrderNumber, OpenDate, Amount, Amex.AmexNum, Type, Account.AccountID, SalesResp, SalesID
From Amex
join Compass.dbo.Account Account on left(OrderNumber,10) = right(MerchantNumber,10)
Join Profitability Prof on Prof.MerchantNum = Account.merchantNumber
and Prof.ReportDate = '11/30/02'
where Amount > 0
and Amex.ReportDate = '11/30/02'
and Account.AccountID in (
Select AccountID from @Tmp
)
December 11, 2002 at 2:52 pm
Unfortunately I could go on all day on potential issues outside of the hotfix. Specifically which hotfixes have you installed that you feel could be involved.
Also, take a look at the Execution Plan on each and compare. But alas, without your DB desgin and a significant amount of test data in comparison and without knowing if the execution plan changed in any way you may never know. On top of that you really have to test the query under the conditions before the hotfixes, add each hotfix (seperately) and test and see when it dives off.
Hopefully thou, if you can list the hotfixes I can test against a server below that which I plan to test some things on that may can suffer at your possible issue. If not I maybe someone can check also. But the most key element you are stating is the hotfixes.
Lastly, before you give up hope run DBCC DBREINDEX, sp_updatestates, and DBCC UPDATEUSAGE. It could just simply be a maintainence issue that showed up afterwards.
December 12, 2002 at 3:16 am
A few comments questions.
How big are the three tables?
Do you have an index on Amex.ReportDate and Prof.ReportDate ?
You have no relation between your sub query and the main query so your qubquery is being evaluated for each record in the main query.
And finally this "left(OrderNumber,10) = right(MerchantNumber,10)" means no index can be used to select data from the account table.
You will be better off using "OrderNumber LIKE RIGHT(MerchantNumber,10) & '%'"
or use computed columns.
if you query is badly optimized the slight changes in data can cause huge differences in performance.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 12, 2002 at 12:51 pm
So I was preparing some responses to your questions, and I noticed that there was not an index on the Amex table, so I added the index ( on ReportDate) and tried the query. It flew. ( there is about 5000 records in that table ). I then took the index off and ran the query again ( again it flew ). Perhaps it updated the stats so it was able to pick a better plan? I went over to my other server and ran the same query with the same data ( without first creating the index ) and it ran fairly quickly as well.
I think there may be something else going on here. I had this problem last month during the monthly process, I ran the whole process and it usually takes 15 minutes or so, after an hour I figured something was wrong, and stopped it and started it again step by step.. Actually Procedure by Procedure. Each step ran fine until one that was filling in a bunch of rows. I contained a bunch of insert statements that join some tables to insert rows where the ID is not already in the table, that seemed to just hang. SO I opened up that proc and began running the statements one by one, each ran in less than a minute a piece. Then later on, I had to re-run that process, and again it would not run that step, I tried the same method of running them individually and this time it did not work.. After spending a day trying to figure it out, I just re-wrote them using the table variable for the in clause, which worked out fine.
anyway, the point of this is that it does not seem to be a consistent problem, there must be some other factor contributing. Maybe the statistics were off or something.
Thank you for your help, I will post back to this if I come up with anything else on this, I would like to try some more experiments on some of the other queries that were causing me problems and see if I can find anything there.
Thanks!
Curt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply