July 20, 2007 at 12:20 pm
Hello ladies and gentlemen!
I have a little problem that I really am quite confused about. I am running a sql 2000 DTS package that, in short, copyies all data from the source to the destination and then deletes the original data from the source. Yep, Archiving indeed. I get all the way to the last step, (my final delete step from the source) and it takes FOREVERRRRRR. I have created an Archive bit field to key off of. To no success, I must stop the package. In the table are about 37,000 rows(where Archive=1). It is currently deleting 1400 rows every 40mins. At that rate, many hours will be needed to complete this package? Here is the script that I am useing. Has anyone run across this before?
DELETE
FROM
Ship
WITH(ROWLOCK)
WHERE
Archive = 1
July 20, 2007 at 12:23 pm
What's the total number of rows in the table? Why rowlock? Is 'archive' indexed? How many other operations are going on on this table while the delete is running and of what type are they? How many indexes are on the table?
July 20, 2007 at 12:32 pm
There are about 500,000 rows in the table. The archive database is an exact replica of the production database. So yes, the table is indexed. The table is on a separate server with no activity. So no other operations are being conducted besides the delete. A good handful + of indexes reside on this table. Process: Restore DB to provide same structure for archive DB, truncate selected tables to allow for archiveing, and then copy data from primary to the archive DB and then delete from the primary except the current year.
July 20, 2007 at 12:45 pm
Is the archive bit indexed? Otherwise, you need to do a table scan just to find the records to delete. I don't think the rowlock provides any benefit and you should try the operation without it as a first step. If there are a lot of indexes on the table, it might be better to drop the non-clustered ones first, do the delete, and then put the indexes back.
July 20, 2007 at 12:47 pm
Good thinking, I'll give it a shot.
Thanks!
July 20, 2007 at 1:26 pm
David's advice is good. I would start by checking the Archive bit for index first. My guess is that this column is not indexed and as David said, SQL Server must perform a table scan to do the delete. So create an index on your Archive bit before going through the effort of the clustered index drop/add. Is there a delete trigger on your table? 1400 rows in 40 minutes sound painfully slow even for a table scan of 500,000 rows.
July 20, 2007 at 1:37 pm
That did it, thanks to all!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply