Run time = 17 hours and counting

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 2000 Forums

SQL Server Development (2000)

Run time = 17 hours and counting

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-02-22 : 12:35:01

I'm running some cleanup on 8 million records that checks another table with 5k records to see if the names match up. It's taking way too long. Anyone have any suggestions?

CREATE PROC [dbo].[spFixEmails]

as

delete
from raw
where email in (select r.email
from raw r
left outer join emp e
on r.email like e.name + '%' or
r.email like e.name2 + '%' or
r.email like e.name3 + '%' or
r.email like e.name4 + '%'
where r.email like e.name + '%' or
r.email like e.name2 + '%' or
r.email like e.name3 + '%' or
r.email like e.name4 + '%')
GO

Kristen
Test

22859 Posts

Posted - 2005-02-22 : 15:24:35

I'd use a JOIN rather than the IN. I'd also restrict it to delete, say, 1,000 rows at a time and put it in a loop until no further rows were deleted.

Have you checked the profile to see what indexes etc. might be used to improve performance?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 15:37:23

How big is the database?

Controlled delete:
http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

The controlled delete is preferred in this case so as not to impact other users and also in case at the very end a rollback is done. If it's in one transaction as you have, then everything would get rolled back. With the controlled delete, just that last batch would get rolled back, at least you wouldn't have to start from scratch.

Tara

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-02-23 : 09:17:09

Would it not be better/simpler to go with something like

delete raw
from raw r
left outer join emp e on r.email = e.name
where r.email like e.name + '%' or r.email like e.name2 + '%' or r.email like e.name3 + '%' or r.email like e.name4 + '%'

I also agree with the 'controlled-volume deletes' suggestion.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-24 : 00:24:59

Can you put the 5k of records in a table variable at least it would'nt IO the disk... I would only use the minimum number of columns

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources