Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
How to optimize massive DELETE operation
Message
 
 
À
26/08/2011 06:32:41
Information générale
Forum:
ASP.NET
Catégorie:
Bases de données
Divers
Thread ID:
01521854
Message ID:
01521893
Vues:
43
>Hi,
>I have SQL Server 2008, and want to delete 200 000 records using DELETE FROM [table] WHERE [table].[manufacturer_id]=@tnManufacturerId
>And this operation is too slow!.. more then 1 minute.
>I had try to delete it partial within WHILE.. by 1000 records per transaction, but it bring me no performance.
>I had clustered unique index by ID and also my manufacturer_id I have an index.
>
>Is there way to increase performance of DELETE FROM ?
>
>Thanks
>Denis Gavrikov

For this reason I don't delete the records right away, but flag the records as deleted:
UPDATE [table] SET DeletedFlag = 1 WHERE id = 1

But that means you have to add the field DeletedFlag and modify your queries to include
WHERE DeletedFlag = 0
everywhere where you don't want to see the "deleted" records.

Once it a blue moon, we optimize the table by running a DELETE FROM [table] WHERE DeletedFlag = 1. This can be done during the night and should not disturb anybody.
Christian Isberner
Software Consultant
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform