deleting rows from tables 3

[dky1e]

Hi,

I am working an a set of querries that delete rows from very large tables.

I am using
"delete from tablename where value in ( some set)"
and the process takes for ever, is there a way to improve the perfomance. Creation of the "some set" is fairly quick, but the ins and not ins take forever.

Is there a faster alternative???

Thank you,
Kyle Dabrowski.

 

[Guest_imported]

I believe you can turn of "Logging" in some situations.
Logging defintely affects performance.
Look up BOL for correct procedures.

 

[tlbroadbent]

You cannot totally turn off logging and in most cases you don't want to turn off logging. There is no rollback and no transaction recovery without logging. Even if a database is set in Simple recovery Mode (SQL 2000) or the Trunc. Log on Checkpoint Option is On (SQL 7 and earlier) SQL must still log transactions in order to enable rollback.

In (<List>) is slow. Try either of these alternatives for faster performance - Where Exists Or Inner Join.

--Example: Where Exists
--Use explicit transactions
Begin Transaction

--Lock the table at the start of the mass delete
--This increases performance because SQL must
--only obtain one lock rather than multiple
--page locks
Delete tablename With (tablockx holdlock)
From tablename t
Where Exists
(Select * From AnotherTable Where value=t.value)

--Example: INNER JOIN
Begin Transaction
Delete tablename With (tablockx holdlock)
From tablename t
Join AnotherTable a
On t.value=a.value
Commit

As Scotty_Roo mentioned, logging affects performance. The primary reason for this is the physical IO required. When a large number of updates are done on a database, SQL Server may need to grow the log file. Growing a log file to handle mass updates can cause several problems - slow performance and running out of disk space are the two most frequent problems encountered.

SQL performs smaller sized batches more efficiently that large batches. Rather than updating 10M rows in one SQL statement, SQL will run 10 batches of 1M much faster. 100 batches of 100K rows will usually perform even better.

Here is is an example script that shows how to perform multiple small batches of updates.

--Use SET ROWCOUNT to limit the number of
--rows updated by a SQL statement
Set rowcount 100000

Declare @rc int
Set @rc=1

While @rc>0
Begin
Begin Transaction

Delete tablename With (tablockx holdlock)
From tablename t
Where Exists
(Select * From AnotherTable
Where value=t.value)

--Get number of rows deleted
Set @rc=@@rowcount

Commit
End

--Reset rowcount to 0 which means unlimited
Set rowcount 0

Let me know if you have any questions about these comments or examples. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[dky1e]

I am not sure how the script works to delete all the required rows. What is the purpose of reseting rowcout to 0 at the end?

 

[ColinM]

Resetting rowcount to 0, basically switches rowcount off. It will return all rows requested.

Another method may be to select all the rows into a new table, then use the TRUNCATE TABLE command. This is an unlogged command. And therefore doesn't log every row deleted, so it is a lot faster. Just make sure you do full backups!

 

[tlbroadbent]

Truncate is a non-logged operation. However, the Select Into will still be minimally logged in order to enable rollback.

Depending on the percentage of rows to be deleted, ColinM's suggestion may prove faster than the batch approach that I selected. You would want to select the rows that are to be kept into the new table. Then Truncate the old table and insert the rows from the new table to the original table. However, the inserts will also use the log and may cause the same problem that I addressed.

A faster option would be to select rows to be kept into a new table. Then Drop the original table and rename the new table. Of course, you'll need to make sure indexes, triggers and permissions are scripted and the scripts run after creating the new table. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.