March 15, 2011 at 7:55 am
Hi,
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is the table is huge - 10 million rows, and it takes over an hour to complete. Is there any way of making it faster without:
* Using Truncate
* Disabling or dropping indexes?
The t-log is already on a separate disk.
Any suggestions welcome!
March 15, 2011 at 7:57 am
you could do it batches using a where clause limiting the PrimaryKey to groups in the delete statement.
I have to ask though, why can you not use Truncate it would be ideal for this situation..?
March 15, 2011 at 8:08 am
I'm wondering, why is batch delete faster than one statement? I'm assuming it is something to do with the transaction log...
Customers are not happy with truncate being used in this situation because they want everything logged.
Also, the table doesn't actually have a primary key (although it does have 3 non-unique, non-clustered indexes).
March 15, 2011 at 8:17 am
bcp the data out to file, or send to backup DB.
Script the table and all constraint, triggers, indexes.
Drop / create.
March 15, 2011 at 9:29 am
Sadly, I cannot change the database structure or perform any DDL operations.
Don't think I can use bcp either - it has to be within a transaction (in a sproc) in T-SQL.
Thanks though!
March 15, 2011 at 9:33 am
Run the delete in smaller batches (top 10 000)
Run as long as @@rowcount > 0.
That was my whole point about bcp, you can reimport if something goes wrong. Then truncate or drop are not an issue.
March 15, 2011 at 9:35 am
tuseau (3/15/2011)
I'm wondering, why is batch delete faster than one statement? I'm assuming it is something to do with the transaction log...Customers are not happy with truncate being used in this situation because they want everything logged.
Also, the table doesn't actually have a primary key (although it does have 3 non-unique, non-clustered indexes).
Define LOGGED? Truncate IS logged. It can be rolled back if needed (assuming you start a transaction)
March 15, 2011 at 9:36 am
Truncate Table is logged, it's just that the pages are deallocated instead of each row being logged as with the delete. For proof of this, try rolling back a truncate statement:
Begin tran
Truncate table yourtesttable
Rollback tran
Select count(*) from yourtesttable
If the truncate was not logged then it couldn't rollback.
March 15, 2011 at 9:41 am
Option Z
Rename old table, drop all constraints (name conflicts)
Then create new table with all constraints and indexes.
You still have a backup of the table and you have an empty table too.
BTW deleting 10 M rows SHOULDN'T take 1 hour, period. 1 Min would already be reallllllllly pushing it. You probably have ressources problems somewhere or you have locks in the table.
March 15, 2011 at 12:31 pm
Toby White (3/15/2011)
Truncate Table is logged, it's just that the pages are deallocated instead of each row being logged as with the delete. For proof of this, try rolling back a truncate statement:Begin tran
Truncate table yourtesttable
Rollback tran
Select count(*) from yourtesttable
If the truncate was not logged then it couldn't rollback.
Thanks, but I have to blame microsoft's documentation for perpetuating this myth. From msdn:
http://msdn.microsoft.com/en-us/library/aa260621%28v=sql.80%29.aspx
"Because TRUNCATE TABLE is not logged, it cannot activate a trigger. "
But then I read up on this a bit, and it appears truncate can be rolled back WITHIN AN EXPLICIT TRANSACTION, but not after the transaction has completed, according to Pinal Dave:
Kind of confusing...
March 16, 2011 at 4:13 am
Ninja's_RGR'us (3/15/2011)
Option ZRename old table, drop all constraints (name conflicts)
Then create new table with all constraints and indexes.
You still have a backup of the table and you have an empty table too.
BTW deleting 10 M rows SHOULDN'T take 1 hour, period. 1 Min would already be reallllllllly pushing it. You probably have ressources problems somewhere or you have locks in the table.
So, the reason I can't perform DDL statements or use truncate is because the application is being designed for concurrent transactions. Truncate will interfere, and obviously so will dropping tables, renaming, disabling indexes etc.
I'm using a TABLOCKX on the table when I delete - also, I'm testing in an isolated development environment, so nothing else can be locking the table... interesting that you say it should only take a minute or so. Does the fact that it is taking place within a single transaction - along with a ton of other reads & writes - have anything to do with it?
March 16, 2011 at 4:39 am
tuseau (3/16/2011)
...So, the reason I can't perform DDL statements or use truncate is because the application is being designed for concurrent transactions. Truncate will interfere, and obviously so will dropping tables, renaming, disabling indexes etc.
I'm using a TABLOCKX on the table when I delete - also, I'm testing in an isolated development environment, so nothing else can be locking the table... interesting that you say it should only take a minute or so. Does the fact that it is taking place within a single transaction - along with a ton of other reads & writes - have anything to do with it?
That sounds like a contradiction: there is a need to have concurrent transactions but TABLOCKX is used for the delete statement...
It might be worth looking into partitioned tables, specifically the "Sliding-Window Scenario" (http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx#sql2k5parti_topic24). You could move the whole partition to a staging table. Most probably a faster option than DELETE.
Disclaimer: I have not tested the scenario as described in the link provided above.
March 16, 2011 at 6:43 am
I agree, there is a lot of confusion about logging with truncate table. Let's discuss your scenario related to the issue at hand.
In your scenario about rolling back transactions that have already been committed, how would truncate table be different from delete? There is no way to rollback a committed transaction from either scenario. Restoring to a point in time would be accomplished in the exact same way. Restore from a full back up, optionally from a differential backup, and then from log backups with stopat the point in time you want from the last log backup. delete/truncate does not impact this process.
March 16, 2011 at 6:53 am
Now there are some contridictions here that need to be clarified. You want to delete all the records from the table while new records are being added.
What would really help is to see the structure of the table (including indexes) and some sample data that is representative of the real data but not real data. You said there is no primary key? Is there some set of column(s) that will uniquely identify each row?
March 16, 2011 at 7:01 am
Just to add to what Lynn is saying. How do you identify the rows that need to be deleted?
Once you have that figured out it's easy to code the delete that won't interfere with anything else...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply