Existe-t-il une bibliothèque comme SqlBulkCopy pour supprimer un grand nombre de lignes en C # et SQL Server?

Vous pouvez créer un tableau supplémentaire avec une colonne pour vos ID cibles, et peut-être aussi un ID de travail. Ensuite, vous pouvez insérer vos identifiants dans ce tableau. Vous vous retrouverez avec environ 30 000 lignes dans le nouveau tableau: une pour chaque ID à supprimer. Si les ID de votre table ne sont pas la clé primaire, c'est également le moment d'effectuer cette traduction. Vous pouvez laisser cette partie de l'opération être un peu lente si vous en avez besoin, car elle n'interférera pas avec d'autres parties de votre base de données en termes de verrous, ou vous pouvez utiliser une technique d'insertion en masse, avec laquelle vous semblez déjà à l'aise.

Une fois cette table remplie, vous pouvez écrire une instruction DELETE qui inclut un JOIN dans cette nouvelle table . D'après mon expérience, ce sera le moyen le plus rapide de terminer votre opération de suppression.

Cela ne ressemble pas à votre situation, mais lorsque vous en avez vraiment besoin pour s'exécuter plus rapidement, vous pouvez également essayer d'exécuter le travail pendant une période de faible charge, ou si vous avez une fenêtre de maintenance, vous pouvez passer à la journalisation en masse pendant un certain temps, bien que ce soit une option de dernier recours.

Une fois la requête terminée, tronquez la table supplémentaire ou supprimez les lignes avec votre ID de travail.

Si ce n'est toujours pas assez rapide, vous pouvez encore améliorer les choses en implémentant des suppressions logiques dans votre application. Une suppression logique se produit lorsque vous ajoutez une colonne dans la table avec un nom tel que IsDeleted ou DeletedDate . Ensuite, pour supprimer une ligne, il vous suffit de mettre à jour la valeur de cette colonne. Vous pouvez également avoir un processus distinct qui s'exécute en arrière-plan pour nettoyer ces enregistrements après un certain temps si vous le souhaitez.

Les suppressions logiques peuvent être beaucoup plus rapides pour traiter des lots plus volumineux. Cependant, cela peut impliquer de réécrire des parties importantes de votre application pour comprendre la différence entre un enregistrement supprimé et un enregistrement actif.

Vous pouvez utiliser l'instruction MERGE pour supprimer massivement des données dans SQL Server.

Vous devez spécifier les clés primaires de la table que vous souhaitez supprimer et la faire correspondre à votre table réelle. Ensuite, vous pouvez décider quoi faire avec les enregistrements correspondants ou non correspondants, dans le cas où vous les supprimez en cas de correspondance:

create table MyTable ( id int primary key, data varchar(5) )
insert into MyTable values (1, 'a'), (2, 'b'), (3, 'c')

merge MyTable as t
using
(
    select * from
    (
        values (1), (2)
    ) V(id)
) as x
on x.id = t.id
when matched then delete;

select * from MyTable -- only pk 3 left

Cela fonctionne très bien lors de la suppression d'une grande quantité de données et il est assez facile à implémenter en C #, bien qu'il n'ait pas les mêmes performances que SqlBulkCopy .

MERGE documents: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

Je réutiliserais normalement un type de table personnalisé dans le cadre d'un ensemble CRUD étendu de procédures stockées lorsque cette situation se produit.

DECLARE @RecordCount INT = 100000
;
WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(10))dt(n)),
R2(N) AS (SELECT 1 FROM R1 a, R1 b),
R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
R4(N) AS (SELECT 1 FROM R3 a, R3 b),
Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R4)

INSERT
INTO _DeleteTest
SELECT Number,'Hey' FROM Tally
WHERE
    Number<=100000

DECLARE @T TABLE(ID INT)
INSERT INTO @T SELECT ID FROM _DeleteTest

DELETE D
FROM _DeleteTest D
INNER JOIN @T P ON P.ID=D.ID


(100000 rows affected)

(100000 rows affected)

(100000 rows affected)

Completion time: 2020-11-13T13:53:54.2883938-05:00

Bien sûr, le kilométrage peut varier en fonction du nombre et des types d'index et de l'occupation du serveur. J'ai couru ceci contre un serveur assez chargé. J'ai défini le seuil maximum à 100K enregistrements et il a construit les données et effectué l'opération de suppression en moins d'une seconde.

CREATE PROCEDURE MyTableDeletByPrimaryKeyInBulk(@PrimaryKeyTable IntegerIDTable)
AS 
    DELETE D
    FROM MyTable D
    INNER JOIN @PrimaryKeyTable P ON P.ID=D.TableID

Je ne me souviens pas de voir un BulkDelete, mais selon [Microsoft] [1] https://social.technet.microsoft.com/wiki/contents/articles/20651.sql-server-delete-a-huge-amount-of- data-from-a-table.aspx [1]:

Vous appliquez simplement une table tronquée TableName, c'est comme un Bulk et videz tous les enregistrements de la table, si ce n'est pas comme ça, suivez les conseils précédents de @ Phate01

Utiliser simplement une suppression plus petite dans la boucle fonctionne pour moi dans le scénario «grande suppression»

plus: cela ne verrouille pas toute la table pendant une longue période.

inconvénients: aucun avantage si utilisé en une seule transaction

--declare @i int =0
SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    --set @i=@i+1
    --print(@i)
    DELETE TOP (5000) FROM yourtable 
    WHERE ...
    
END

30K est un petit nombre de lignes pour SQL, je l'utilise pour supprimer des millions de lignes dans une table énorme.

Coluld être un autre problème comme une mauvaise indexation, verrouillage, table de tas ECC.