L'instruction Delete dans SQL est très lente
J'ai des déclarations comme celle-ci qui chronométrent:
DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')
J'ai essayé de faire un à la fois comme ceci:
DELETE FROM [table] WHERE [COL] IN ( '1' )
Et jusqu'à présent, il est à 22 minutes et continue.
La table contient 260 000 lignes et comporte quatre colonnes.
Quelqu'un a une idée pourquoi ce serait si lent et comment l'accélérer? J'ai un index non unique et non clusterisé sur le [COL] que je fais le WHERE on. J'utilise SQL Server 2008 R2
Mise à jour: je n'ai pas de déclencheurs sur le table.
11 réponses
Choses qui peuvent provoquer une suppression lente:
- suppression d'un grand nombre d'enregistrements
- de nombreux index
- index manquants sur les clés étrangères dans les tables enfants. (merci à @ CesarAlvaradoDiaz d'avoir mentionné cela dans les commentaires)
- blocages et blocage
- déclencheurs
- cascade delete (ces dix enregistrements parents que vous supprimez pourraient signifier des millions d'enregistrements enfants sont supprimés)
- journal des transactions à développer
- de nombreuses clés étrangères pour vérifier
Donc, vos choix sont de savoir ce qui bloque et de le réparer ou d'exécuter les suppressions en dehors des heures où elles n'interféreront pas avec la charge de production normale. Vous pouvez exécuter la suppression par lots (utile si vous avez des déclencheurs, une suppression en cascade ou un grand nombre d'enregistrements). Vous pouvez déposer et recréer les index (mieux si vous pouvez le faire en dehors des heures).
-
Désactiver la contrainte
ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;
-
Désactiver L'Index
ALTER INDEX ALL ON [TableName] DISABLE;
-
Reconstruire L'Index
ALTER INDEX ALL ON [TableName] REBUILD;
-
Activer la contrainte
ALTER TABLE [TableName] CHECK CONSTRAINT ALL;
De Supprimer à nouveau
Supprimer beaucoup de lignes peut être très lent. Essayez d'en supprimer quelques-uns à la fois, comme:
delete top (10) YourTable where col in ('1','2','3','4')
while @@rowcount > 0
begin
delete top (10) YourTable where col in ('1','2','3','4')
end
L'Action Préventive
Vérifiez avec l'aide de SQL Profiler
la cause profonde de ce problème. Il peut y avoir Triggers
provoquant le retard dans l'exécution. Il peut être n'importe quoi. N'oubliez pas de sélectionner Database Name
et Object Name
lors du démarrage de Trace
pour exclure l'analyse des requêtes inutiles...
Filtrage Des Noms De Base De Données
Filtrage De Table/Procédure Stockée/Nom Du Déclencheur
Mesures Correctives
Comme vous l'avez dit votre table contient 260 000 enregistrements...et IN Predicate
contient six valeurs. Maintenant, chaque enregistrement est en cours de recherche 260 000 fois pour chaque valeur dans IN Predicate
. Au lieu de cela, il devrait être la jointure intérieure comme ci-dessous...
Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id
Insérer le IN Predicate
valeurs dans un Temporary Table
ou Local Variable
Si la table à partir de laquelle vous supprimez a des déclencheurs BEFORE/AFTER DELETE, quelque chose pourrait causer votre retard.
De plus, si vous avez des clés étrangères référençant cette table, des mises à jour ou des suppressions supplémentaires peuvent se produire.
Il est possible que d'autres tables aient une contrainte FK sur votre [table]. La base de données doit donc vérifier ces tables pour maintenir l'intégrité référentielle. Même si vous avez tous les index nécessaires correspondant à ces FK, vérifiez leur montant.
J'ai eu la situation lorsque NHibernate a incorrectement créé dupliqué FK s sur les mêmes colonnes, mais avec des noms différents (ce qui est autorisé par SQL Server). Il a considérablement ralenti l'exécution de L'instruction DELETE.
Est-ce que [COL] est vraiment un champ de caractères contenant des nombres, ou pouvez-vous vous débarrasser des guillemets simples autour des valeurs? @Alex a raison que IN est plus lent que=, donc si vous pouvez le faire, vous serez mieux lotis:
DELETE FROM [table] WHERE [COL] = '1'
Mais mieux encore utilise des nombres plutôt que des chaînes pour trouver les lignes (sql aime les nombres):
DELETE FROM [table] WHERE [COL] = 1
Peut-être essayer:
DELETE FROM [table] WHERE CAST([COL] AS INT) = 1
Dans les deux cas, assurez-vous d'avoir un index sur la colonne [COL] pour accélérer l'analyse de la table.
Vérifiez le plan d'exécution de cette instruction delete. Jetez un oeil si index seek est utilisé. Aussi, Quel est le type de données de col?
Si vous utilisez un mauvais type de données, modifiez l'instruction update (comme de ' 1 'à 1 ou N '1').
Si l'analyse d'index est utilisée, pensez à utiliser un indice de requête ..
J'ai lu cet article, il était vraiment utile pour le dépannage de tout type d'inconvénients
Https://support.microsoft.com/en-us/kb/224453
C'est un cas de waitresource Clé: 16: 72057595075231744 (ab74b4daaf17)
-- First SQL Provider to find the SPID (Session ID)
-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57
select * from sys.databases where database_id=16
-- with Waitresource check this to obtain object id
select * from sys.partitions where hobt_id=72057595075231744
select * from sys.objects where object_id=2105058535
Si vous supprimez tous les enregistrements de la table plutôt que quelques-uns, il peut être beaucoup plus rapide de simplement supprimer et recréer la table.
Ouvrez CMD et exécutez cette commande
NET STOP MSSQLSERVER
NET START MSSQLSERVER
Cela redémarre L'instance SQL Server. essayez d'exécuter à nouveau après votre commande delete
J'ai cette commande dans un script batch et l'exécute de temps en temps si je rencontre des problèmes comme celui-ci. Un redémarrage normal du PC ne sera pas le même, donc redémarrer l'instance est le moyen le plus efficace si vous rencontrez des problèmes avec votre serveur sql.