Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Batch Delete is Slow :angry: Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 335, Visits: 1,141
so what is the better solution to delete records in sql server 2000
Post #1556090
Posted Friday, March 28, 2014 12:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 15,646, Visits: 28,028
As I said before, your choices in 2000 are more limited. Filter the data on additional criteria to reduce the batch size so you're deleting in smaller chunks and providing the optimizer the ability to come up with a good plan. Using ROWCOUNT doesn't change what the optimizer chooses and if the filtering criteria is not there, the optimizer will choose to scan the data, which is slow.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1556100
Posted Saturday, March 29, 2014 10:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 335, Visits: 1,141
Grant Fritchey (3/28/2014)
As I said before, your choices in 2000 are more limited. Filter the data on additional criteria to reduce the batch size so you're deleting in smaller chunks and providing the optimizer the ability to come up with a good plan. Using ROWCOUNT doesn't change what the optimizer chooses and if the filtering criteria is not there, the optimizer will choose to scan the data, which is slow.


What benefit i will be getting if i move to sq 2008.
Post #1556227
Posted Saturday, March 29, 2014 11:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
For this particular issue, possibly no real benefit. The problem we are having is that we can't see what you see which means all we can give you are shots in the dark. You have posted nothing that will help us tell you what may help you improve your process. There is no DDL for the table, any indexes on the table, foreign keys (both to and from the table).

Seeing the actual execution plan would also help, but by the time you had that the DELETE would be done as well, unless this is a recurring process that needs to be improved.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1556231
Posted Sunday, March 30, 2014 2:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 15,646, Visits: 28,028
The only suggestion I had for the higher versions of SQL Server is that you could use the ROW_NUMBER to break up the deletes into smaller groups. You still have to have mechanisms for filtering and choosing how to break the data into smaller groups. Plus everything Jason said.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1556301
Posted Sunday, March 30, 2014 8:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
There is absolutely no need to use SET ROWCOUNT in SQL Server 2000. SELECT TOP works just fine except that you can't use a variable to define the TOP number of rows.

Yes, FKs will slow down any deletes. Turning off the FKs during deletes might make the deletes go faster but it will allow people to put in bad data if you turn off the foreign keys, so don't do it. Just be patient and let the DELETE loop do its job.

Also, don't make the stop condition to look for 0 rows deleted. Make the stop condition look for a number of rows that's less than the batch size. Only the last batch controlled by the loop will have that condition. If you look for 0 rows to be deleted, then you have an extra iteration of the loop and it will be the longest iteration because it will likely take longer to find that it has nothing to do.

If you are deleting more rows than what you'll end up with, consider not doing deletes. Consider copying the rows you want to keep to another table, rebuilding the indexes and FK's, renaming the original table to tablename_Old and rename the new table to be what the original table name was and, if everything went ok, then drop the tablename_Old table and you're done.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556321
Posted Sunday, April 20, 2014 11:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 335, Visits: 1,141
Lynn Pettis (3/29/2014)
For this particular issue, possibly no real benefit. The problem we are having is that we can't see what you see which means all we can give you are shots in the dark. You have posted nothing that will help us tell you what may help you improve your process. There is no DDL for the table, any indexes on the table, foreign keys (both to and from the table).

Seeing the actual execution plan would also help, but by the time you had that the DELETE would be done as well, unless this is a recurring process that needs to be improved.


Sorry for the late replay. i was assigned another work.so came back again here to this ..
have attached tables and execution plan on delete statement.
It is a simple straightforward delete no joins.


  Post Attachments 
Delete_tab.txt (3 views, 5.89 KB)
plan.sqlplan_del.sqlplan (1 view, 8.61 KB)
Post #1563386
Posted Monday, April 21, 2014 6:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
yuvipoy (4/20/2014)
Lynn Pettis (3/29/2014)
For this particular issue, possibly no real benefit. The problem we are having is that we can't see what you see which means all we can give you are shots in the dark. You have posted nothing that will help us tell you what may help you improve your process. There is no DDL for the table, any indexes on the table, foreign keys (both to and from the table).

Seeing the actual execution plan would also help, but by the time you had that the DELETE would be done as well, unless this is a recurring process that needs to be improved.


Sorry for the late replay. i was assigned another work.so came back again here to this ..
have attached tables and execution plan on delete statement.
It is a simple straightforward delete no joins.


So, your DELETE statement looks like this:

DELETE FROM SE_CD_MEASUREMENT WHERE RUN_START_TIME <= 13121331223

Problem is that your DDL doesn't match this query. Which table are you trying to delete from? What column is RUN_START_TIME? What type of value is RUN_START_TIME? Is it an BIGINT, INT, what? What does the value indicate, a specific date/time from a specified point in time such as milliseconds since midnight 1970-01-01?

Still don't have enough to really help you. Only suggestion I have, is stop trying to obfuscate your problem so much that no one can even figure out what it is you are trying to accomplish.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1563462
Posted Monday, April 21, 2014 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
Moved to SQL 2000






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563530
Posted Monday, April 21, 2014 10:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 335, Visits: 1,141
Sorry i did not replace my table plan, now i replaced my plan with the table which i have give.
Post #1563677
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse