| Author |
Topic |
|
avijit_mca
Posting Yak Master
109 Posts |
 Posted - 2010-03-08 : 10:34:14
|
| I have table which contain ten million of data.now i hv delete say 2 million record from that table.what are the steps r required for fast deletion..delete from tab1 where city='Singapore'where city colum already defined by non clustered index.please guide me how we can write query for boost perfrmanceRegards,avijit |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 10:40:14
|
| if you've index on it then it will perform well. Are you facing any performance bottlenecks?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-03-08 : 10:41:27
|
| execute the "deletes" in smaller batches. less SQL logging. |
|
|
|
avijit_mca
Posting Yak Master
109 Posts |
Posted - 2010-03-08 : 10:47:57
|
| hi visakh,i wnat to know any others techniqs are available or not for making delete operation more fast.because its taking time.Regards,avijit |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 10:51:51
|
| did you try suggestion of Andrew?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
avijit_mca
Posting Yak Master
109 Posts |
Posted - 2010-03-08 : 11:03:06
|
| no..i did not try which suggested by Andrew.Regards,avijit |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 11:18:47
|
| try batch deletion as He suggested------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-03-08 : 11:29:04
|
| i wnat to know any others techniqs are available or not for making delete operation more fast.If you are sure 100% you do not need the deleted data anymore, change recovery mode to simple, then run your delete script. It'll be much faster because transaction is minimum logged. After done, you can change the recovery mode to the previous one.Using truncate is better but it does not suppose where clause, unfortunally. |
|
|
|
avijit_mca
Posting Yak Master
109 Posts |
Posted - 2010-03-08 : 23:39:53
|
| Thanks All....namman & AndrewMurphy :its really good idea .Its really helpful. But i would like to know more about deletion when deleted table values are link with others table.i mean say deleted table is parent table which related to others child table.Regards,avijit |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-09 : 03:04:09
|
| "If you are sure 100% you do not need the deleted data anymore, change recovery mode to simple, then run your delete script. It'll be much faster because transaction is minimum logged. After done, you can change the recovery mode to the previous one"I don't see this makes any difference (unless you also use batch delete, as recommended above) - maybe I am missing something? |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-03-09 : 14:12:12
|
Hi KristenFor my understanding, batch delete method is to divide the process into small parts, then run them one by one. This is good for the system resource. But all deleted data still has been logged in the log file as using regular method, so the total cost of those is still the same as running them all at a time.My suggestion is changing the recovery model before deleting. The main different here is about log. If we delete in full model, all deleted data has been logged in the log file, does't matter we run 1 delete command or we divide it into small batches. This may cost a lot of resource. If we change to simple mode, transactions are only minimum logged. That's why.The inconvinence is that it should be used offline because on production we need full model for backup/restore.That is my idea, and of course it may be wrong  Nice to discuss with you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-09 : 14:42:26
|
| " If we change to simple mode, transactions are only minimum logged. "Minimum logged??So what happens if there is a power cut in the middle?When SQL restarts it rolls back the transaction.So it has to have fully logged the transaction, the same as if it was using Full Recovery Model.So ... the size of a SINGLE transaction, in the log file, is the same - whether Recovery model is SIMPLE or FULL.IMHO there is therefore no point changing the Recovery model - UNLESS you also use batch deletion - in which case the batches will be smaller, and although each batch is logged, the space used in the TLog is then REUSED by the next batch - thus saving space (with the slight complication of when a Checkpoint may occur, so this may not perfectly only use one batch's TLog space, but it uses much less resources than Full Recovery Model).But if you do NOT use Batch Delete then there is NO difference - to be best of my knowledge. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-03-09 : 15:01:17
|
quote:
Originally posted by avijit_mca
Thanks All....namman & AndrewMurphy :its really good idea .Its really helpful. But i would like to know more about deletion when deleted table values are link with others table.i mean say deleted table is parent table which related to others child table.Regards,avijit
Index ALL Foriegn Keys! Let's say you execute a delete to an Order record. If Items.Order_ID is not indexed, and is an FK to the Orders table, SQL Server will have to search the entire Items table to see if the delete would break the FK relationship! |
|
|
|
avijit_mca
Posting Yak Master
109 Posts |
Posted - 2010-03-10 : 00:18:27
|
| thanks.Regards,avijit |
|
|
|
|
performance for deleting data