Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Batch Delete is Slow :angry:


Batch Delete is Slow :angry:

Author
Message
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1317
so what is the better solution to delete records in sql server 2000
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1317
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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.

Cool
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)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45171 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1317
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.
Attachments
Delete_tab.txt (4 views, 5.00 KB)
plan.sqlplan_del.sqlplan (2 views, 8.00 KB)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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.

Cool
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)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36184 Visits: 18751
Moved to SQL 2000

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1317
Sorry i did not replace my table plan, now i replaced my plan with the table which i have give.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search