Batch Delete is Slow :angry:

Question

Batch Delete is Slow :angry:

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yuvipoy SSChampion Points: 10469 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 735513 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 735513

April 21, 2014 at 9:37 am

#1707489

Moved to SQL 2000

yuvipoy SSChampion Points: 10469 More actions · Answer 5

Sorry i did not replace my table plan, now i replaced my plan with the table which i have give.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

yuvipoy (4/21/2014)
Sorry i did not replace my table plan, now i replaced my plan with the table which i have give.

But you didn't answer any of the questions. Changing the name of the table and columns doesn't solve the issues presented. There still isn't enough information to do anything but shoot in the dark and hope we give you answer that works.

Ask yourself if you knew nothing about the problem could you answer the question with what is provided.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

yuvipoy (4/21/2014)
Sorry i did not replace my table plan, now i replaced my plan with the table which i have give.

More issues. You renamed the table to TABLE2 and the column to COL10. Issue, there is no COL10 in the DDL for TABLE2. There is a TCOL10 and it is declared as a float. The value you are comparing this column to appears to be a BIGINT not a float.

You still need to answer all the other questions I asked earlier as well.

You also stated in your initial post:

I am deleting some 200000 records from 5000000 rows.

The estimated execution plan indicates that you may actually be trying to delete 3,655,550 rows of data.

Please answer all the questions you have been asked and explain exactly what it is you are trying to accomplish. Without these answers, I am done trying to help. Sorry.