Deleting a large number of records +2gigs 2

[jisaacson]

I have a database of over 20gigs. One of the tables has over 19 million records, as we have not deleted anything since we created the database.

I now want to delete old records. I am concerned with the time it takes to do this. Whenever I have deleted records in the past from other tables, it takes a long time if you delete a lot of records at a time.

There are nine indexes on this table. Would it be a lot faster to remove the indexes, delete the records and then re-index the table?

 

[gmmastros]

Start by reading this...

faq183-3141

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

If you sare talking about wiping the entire table, use TRUNCATE TABLE. Easy & fast....

Silence is golden.
Duct tape is silver.

 

[jisaacson]

No, I don't want to delete all records from the table, just selected ones. This means that I will be deleting by row.

 

[RobertT687]

If you are deleting most of the rows in you table, this approach MAY be a bit faster.
1) SELECT * INTO mynewtable FROM myoldtable WHERE (criteria for rows to keep)
2) TRUNCATE myoldtable
3) INSERT myoldtable SELECT * FROM mynewtable
4) DROP mynewtable
This approach won't work if foreign key relationships exist on 'myoldtable'; you won't be able to TRUNCATE it.

 

[nice95gle]

As the FAQ said that Geroge linked to earlier.

You would do something like this:

--I have it set to 50000 records at a time but you can set it to whatever number you feel comfortable with

SET rowcount 50000
Declare @rc int
SET @rc=50000
While @rc = 50000
Begin
Begin Transaction
	delete from your_table with (tablockx, holdlock)
             where your_field='somevalue'
	Select @rc=@@rowcount
Commit Transaction
End
SET rowcount 0

Robert the way that you stated wouldn't be very Tansaction log friendly.

***WARNING: When doing any kind of delete, please test in a safe environment before running in production***

Well Done is better than well said
- Ben Franklin

 

[SQLDenis]

>>Robert the way that you stated wouldn't be very Tansaction log friendly.


Not true, SELECT INTO and TRUNCATE are both minimally logged operations (only the pointers to the pages will be dealocated)

I am 99.99% sure that what Robert suggested will be many times faster than the delete since there is nothing to log

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

Denis, please correct me if I'm wrong.

Isn't SELECT INTO a fully logged operation unless you are in bulk logged recovery mode?

Thanks

Well Done is better than well said
- Ben Franklin

 

[jisaacson]

Robert,

My data does have foreign keys

 

[SQLDenis]

>>Isn't SELECT INTO a fully logged operation unless you are in bulk logged recovery mode?

yes, you have to be in bulk logged recovery mode

so running

ALTER DATABASE DB_NAME SET RECOVERY BULK_LOGGED


do stuff here

ALTER DATABASE DB_NAME SET RECOVERY FULL

will fix that




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

>>My data does have foreign keys

then you can't use TRUNCATE until you delete the relationship


>>There are nine indexes on this table. Would it be a lot faster to remove the indexes, delete the records and then re-index the table?

yes drop all the indexes except for the one which is your unique key so that you can use that one for the delete query

Nine indexes on 1 table seems a little too much, are you sure you need all of these? Inserts, deletes and updates will be slower with that many indexes




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jisaacson]

Some of the indexes are not used that often, but when a look-up needs to be done, it makes a big difference to the user with the index.

However, I probably should review the indexes I am using to see if some of them can be removed. I'm glad that you reminded me that I can improve performance by reducing some of my indexes.

 

[nice95gle]

Thanks to Denis and a little research, I would like to update my previous statement.

SELECT INTO will only get logged IF you are in FULL recovery mode. I made the assumption that jisaacson's database was set to FULL recovery (most DB’s I’ve seen is set to FULL). So yes Robert's way is the best and fastest way to do your delete.


Well Done is better than well said
- Ben Franklin