How to accelerate delete query

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)?

thanks
meciabAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have no foreign keys pointing to the table, and you want to delet eall the rows:

TRUNCATE TABLE yourtable
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
meciab,
>  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
0
 
meciabAuthor Commented:
Oupps sorry
>  I need to reinitialize each week
    =====> I need to reinitialize all data each week (so delete all the table each week)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

thanks
0
 
OtanaCommented:
Do you have an index on your search field?
0
 
adatheladCommented:
>> 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?!)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
 
meciabAuthor Commented:
"Do you have an index on your search field?"
Yes non clustered
0
 
meciabAuthor Commented:
Thanks for you help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.