June 30, 2008 at 7:16 am
Hi,
We are running into problems with the delete functionality in our application.
Basically, the application is designed in such a way that we have a primary data table that contains virtually all of the applications data. At some of our larger installations, this table has >30 million rows.
When you try to delete a business entity from the system, it can involve removing 30,000+ rows from this table. This table has a number of indexes to support day-day (OLTP type) operation.
Would issuing ROWLOCK on the delete statement(s) help ? or would this just increase memory usage off the scale?
Would partitioning the main table help? We would still end up with 60% of the data in a single table.
Is there anything else you can do to optimize removing the rows from the table? Given appropriate hardware, is it possible to do this in a timely manner ie. <5 minutes whilst the user waits. Or should we be looking at reengineering the delete user interface so you are submitting a delete request that you come back later to check ie. a more asynchronous model.
We are using SQL Server 2000 and 2005.
Thanks in advance,
Matt
June 30, 2008 at 7:25 am
can you post the DML you use for this delete ?
If it is a single delete statement that just performs like a table scan to do its stuff, that may indeed cause lock escalation.
Can you chop the delete in batches ?
SET NOCOUNT ON
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE TableName WHERE
IF @@ROWCOUNT = 0 BREAK
CHECKPOINT
END
SET ROWCOUNT 0
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2008 at 7:29 am
Paritioning may help, without seeing more info on the table design and delete statement I can't be more specific than that.
The async model may very well help. Scheduling the deletes to happen overnight will reduce their impact on normal operations.
Deletes of a lot of rows do take long. Often what's recomended it to batch the deletes and only do a couple thousand at a time. Something like this (untested)
DECLARE @Done int
SET @Done = 0
SET ROWCOUNT 5000
WHILE (@Done = 0)
BEGIN
DELETE FROM SomeTable WHERE SomeConditions -- will delete max 5000 due to the rowcount setting
IF @@ROWCOUNT = 0
SET @Done = 1
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2008 at 7:32 am
I know every case is different, but deleting 20,000 doesnt sound as though it should be taking anything like 5 minutes.
A few thoughts:-
Assuming you are doing a DELETE... WHERE..., are the WHERE columns indexed?
Do any other tables have constraints to the table you are deleting from, and if so, are the constraining columns indexed?
Do you have any delete triggers on the table?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply