Difference bet. 2000 vs. 2005 Stored Procedure

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difference bet. 2000 vs. 2005 Stored Procedure

Author

Topic

eriklang
Starting Member

1 Post

Posted - 2007-02-09 : 15:15:31

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-09 : 15:49:24

a number of things to try

add indexes to the tables
Delete #dr
From #dr dr
left join #included_policies ip
on dr.DirstatPolState = ip.DirstatPolState
and dr.DirstatPolAcctNbr = ip.DirstatPolAcctNbr
...
where ip.DirstatPolState is null

or
select into another table on the matches.
might well be fastest.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources