February 9, 2007 at 1:32 pm
I had a stored procedure on my SQL Server 2000 box that took about 30 minutes to run. When i ported it over to my new SQL Server 2005 test box, it takes about 16 hours to run. The stored procedure basically creates a temp table w/ about 2 million records with all the available accounts. It then creates a 2nd temp table w/ all the active accounts. Finally, it uses a delete statement w/ a simple subquery to delete the in-active accounts. Its during the deleting part of the procedure that the system seems to take the longest but it eventually completes it. Here is the delete statement:
Delete #dr
From #dr dr
Where
dr.DirstatPolState+dr.DirstatPolAcctNbr+
dr.PolSerialNbr+dr.XdirstatPolicyYear
NOT IN
(Select ip.DirstatPolState+ip.DirstatPolAcctNbr+ip.PolSerialNbr+ip.XdirstatPolicyYear
From #included_policies ip)
Any help w/ why it is taking so long would be greatly appreciated.
February 9, 2007 at 5:46 pm
It depends on the server HW and memory..
Also Statitics...
MohammedU
Microsoft SQL Server MVP
February 12, 2007 at 3:18 am
While this doesn't address your question about what architecture change in 2005 causes your delete to slow down, the following should be a lot more efficient. Indexing one or more of the fields you are joining on is worth testing as well.
DELETE #dr
FROM #dr dr
WHERE NOT EXISTS (
SELECT *
FROM #included_policies AS ip
WHERE dr.DirstatPolState = ip.DirstatPolState
AND dr.DirstatPolAcctNbr = ip.DirstatPolAcctNbr
AND dr.PolSerialNbr = ip.PolSerialNbr
AND dr.XdirstatPolicyYear = ip.XdirstatPolicyYear )
February 12, 2007 at 6:09 am
You need to look closely at the execution plans on both servers and examine the differences. SQL takes a lot into consideration when creating n execution plan including hardware, indexes, statistics, etc. Small system differences can have a big impact. You may find that you have done something as simple as put your tempdb in an inefficient place, or you have enough less memory in your test environment that it has to do a lot more disk reads.
Much of this could be determined by examing execution plan differences.
February 12, 2007 at 7:19 am
Irrespective of why the code snippet you produced is running slower on sql2k5, wouldn't it be MUCH faster to simply create a table with the rows you DO WANT in one pass, instead of creating two tables and diff'ing them to get what you want?? Looks like such a query would be a pretty simple join construct. NOT EXISTS can get the optimizer onto a really bad plan.
If you keep the same code, I would create an index on both tables containing at least one field in the join (the most specific one), if not all of them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 12, 2007 at 1:30 pm
Thanks, just changing some of the syntax really made a big difference. I realize the stored procedure is not written very effeciently in the first place but i was just surprised that going from SQL 2000 to 2005 would make sure a difference. Especially considering the new box is alot faster.
Thanks,
Erik
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply