How to accelerate delete query

Posted on 2006-05-05
Medium Priority
Last Modified: 2006-11-18
I have a 10 million records table that I need to reinitialize each week.
This table have 10 fields and an indexed primary key and when I do a "delete from myTab" the log going huge and It takes a while.
Is it possible to accelerate this (by specifiing any bypass mode)?

Question by:meciab
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16613320
>  I need to reinitialize each week

> I do a "delete from myTab" the log going huge

These 2 statements conflict.  Instead of deleteing 10m records, truncate the table and use some insert statements
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 400 total points
ID: 16613322
if you have no foreign keys pointing to the table, and you want to delet eall the rows:


Author Comment

ID: 16613413
Oupps sorry
>  I need to reinitialize each week
    =====> I need to reinitialize all data each week (so delete all the table each week)
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16613427
Is there an other statment wich is accelerating the delete process but where you can include a WHERE?

LVL 11

Expert Comment

ID: 16613511
Do you have an index on your search field?
LVL 23

Assisted Solution

adathelad earned 100 total points
ID: 16613553
>> Is there an other statment wich is accelerating the delete process but where you can include a WHERE?
No. You can either use DELETE (which will log each delete, but accepts a WHERE clause) or TRUNCATE (which doesn't log each delete hence is quicker, but doesn't accept a WHERE clause as it empties a table completely).

If you're saying you don't want all data to be deleted, then you could consider this:

1) Rename existing table (e.g. append with _OLD)
2) Create a new copy of the table structure
3) use an INSERT statement to copy the rows to be kept, into the new table
4) Delete OLD table (DROP TABLE....)

I'm not saying this would be quicker than using a DELETE....WHERE.... statement, in fact it could well be slower in most circumstances. But I was thinking if you had 10m rows, 10000 of which to keep and 9, 990, 0000 to delete, then this may be quicker (i.e. INSERT 10,000 rows, rather than DELETE 9,990,000 rows if that makes sense?!)
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16613983
>so delete all the table each week

as already mentionned, truncate table will be the fastest.
truncate, drop table and/or rename table + create new one have the same constraint:
if there is any foreign key on the table, you have first to drop that constraint.

Author Comment

ID: 16614076
"Do you have an index on your search field?"
Yes non clustered

Author Comment

ID: 16628894
Thanks for you help

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question