Comment supprimer efficacement les lignes sans utiliser la table tronquée dans une table 500 000+ rows

appelant DELETE FROM TableName effacera la totalité de la suppression en une seule opération importante. C'est cher.

Voici une autre option qui supprimera les lignes dans les lots :

deleteMore:
DELETE TOP(10000) Sales WHERE toDelete='1'
IF @@ROWCOUNT != 0
    goto deleteMore

ce que vous voulez, c'est un traitement par lots.

While (select Count(*) from sales where toDelete =1) >0
BEGIN
Delete from sales where SalesID in
(select top 1000 salesId from sales where toDelete = 1)
END

bien sûr, vous pouvez expérimenter ce qui est la meilleure valeur à utiliser pour le lot, j'ai utilisé de 500 - 50000 selon la table. Si vous utilisez cascade delete, vous aurez probablement besoin d'un nombre plus petit que vous avez ces dossiers enfant à supprimer.

une façon que j'ai eu à faire cela dans le passé est d'avoir une procédure stockée ou un script qui supprime n records. Répétez jusqu'à ce que fait.

DELETE TOP 1000 FROM Sales WHERE toDelete='1'

vous devriez essayer de lui donner un indice ROWLOCK pour qu'il ne verrouille pas la table entière. Cependant, si vous supprimez un grand nombre de lignes de verrouillage escalade se produira.

de plus, assurez-vous d'avoir un indice filtré (seulement pour 1 Valeurs) sur la colonne toDelete . Si possible le faire une colonne de peu, pas varchar (ou ce qu'il est maintenant).

DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'

finalement, vous pouvez essayer d'itérer sur la table et supprimer dans morceau.

mise à Jour

puisque bien que les boucles et les suppressions de morceaux sont le nouveau rose ici, je vais jeter dans ma version aussi (combiné avec ma réponse précédente):

SET ROWCOUNT 100
DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'

WHILE @@rowcount > 0
BEGIN
  SET ROWCOUNT 100
  DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'  
END

mon propre point de vue sur cette fonctionnalité serait comme suit. De cette façon, il n'y a pas de code répété et vous pouvez gérer votre taille de morceau.

DECLARE @DeleteChunk INT = 10000
DECLARE @rowcount INT = 1

WHILE @rowcount > 0
BEGIN

  DELETE TOP (@DeleteChunk) FROM Sales WITH(ROWLOCK)

  SELECT @rowcount = @@RowCount
END

j'ai utilisé ce qui suit pour supprimer environ 50 millions d'enregistrements -

BEGIN TRANSACTION     
     DeleteOperation:
     DELETE TOP (BatchSize)
     FROM  [database_name].[database_schema].[database_table] 

     IF @@ROWCOUNT > 0
     GOTO DeleteOperation
COMMIT TRANSACTION

veuillez noter que garder le BatchSize < 5000 est moins cher sur les ressources.

comme je suppose que la meilleure façon de supprimer un grand nombre d'enregistrements est de le supprimer par Primary Key . (Qu'est-ce que Primary Key voir ici )

donc vous devez générer un script tsql qui contient toute la liste des lignes à supprimer et ensuite exécuter ce script.

par exemple le code ci-dessous va générer ce fichier

GO
SET NOCOUNT ON

SELECT   'DELETE FROM  DATA_ACTION WHERE ID = ' + CAST(ID AS VARCHAR(50)) + ';' + CHAR(13) + CHAR(10) + 'GO'
FROM    DATA_ACTION
WHERE  YEAR(AtTime) = 2014

le fichier ouput va avoir des enregistrements comme

DELETE FROM  DATA_ACTION WHERE ID = 123;
GO
DELETE FROM  DATA_ACTION WHERE ID = 124;
GO
DELETE FROM  DATA_ACTION WHERE ID = 125;
GO

et maintenant vous devez utiliser l'utilitaire SQLCMD pour exécuter ce script.

sqlcmd -S [Instance Name] -E -d [Database] -i [Script]

vous pouvez trouver cette approche expliquée ici https://www.mssqltips.com/sqlservertip/3566/deleting-historical-data-from-a-large-highly-concurrent-sql-server-database-table /