SQL Server Optimisation des requêtes de mise à jour pour de gros volumes de données
La mise à jour de très grandes tables peut prendre du temps et parfois des heures à se terminer. En plus de cela, cela peut également entraîner des problèmes de blocage.
Voici quelques conseils pour optimiser les mises à jour de SQL Server sur de gros volumes de données.
- Suppression de l’index sur la colonne à mettre à jour.
- Exécution de la mise à jour par lots plus petits.
- Désactivation des déclencheurs de suppression.
- Remplacement de l’instruction Update par une opération d’insertion en bloc.
Cela étant dit, appliquons les points ci-dessus pour optimiser une requête de mise à jour.
Le code ci-dessous crée une table factice avec 200 000 lignes et les index requis.
CREATE TABLE tblverylargetable ( sno INT IDENTITY, col1 CHAR(800), col2 CHAR(800), col3 CHAR(800) ) GO DECLARE @i INT=0 WHILE( @i < 200000 ) BEGIN INSERT INTO tblverylargetable VALUES ('Dummy', Replicate('Dummy', 160), Replicate('Dummy', 160)) SET @i=@i + 1 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)
Considérez la requête de mise à jour suivante qui doit être optimisée. C’est une requête très simple pour mettre à jour une seule colonne.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
L’exécution de la requête prend 2 minutes 19.
Regardons le plan d’exécution de la requête ci-dessous. En plus de la mise à jour de l’index en cluster, l’index ix_col1 est également mis à jour. L’opération de mise à jour et de tri de l’index prend ensemble 64% du coût d’exécution.
1. Suppression de l’index sur la colonne à mettre à jour
La même requête prend 14 à 18 secondes lorsqu’il n’y a pas d’index sur col1. Ainsi, une requête de mise à jour s’exécute plus rapidement si la colonne à mettre à jour n’est pas une colonne de clé d’index. L’index peut toujours être créé une fois la mise à jour terminée.
2. Exécution de la mise à jour par lots plus petits
La requête peut être encore optimisée en l’exécutant par lots plus petits. C’est généralement plus rapide. Le code ci-dessous met à jour les enregistrements par lots de 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
L’exécution de la requête ci-dessus prend 6 à 8 secondes. Lors de la mise à jour par lots, même si la mise à jour échoue ou doit être arrêtée, seules les lignes du lot en cours sont annulées.
3. La désactivation des déclencheurs de suppression
avec des curseurs peut extrêmement ralentir les performances d’une requête de suppression. La désactivation des déclencheurs Après suppression augmentera considérablement les performances de la requête.
4. Remplacer l’instruction Update par une opération d’insertion en bloc
Une instruction update est une opération entièrement journalisée et cela prendra donc certainement beaucoup de temps si des millions de lignes doivent être mises à jour.Le moyen le plus rapide d’accélérer la requête de mise à jour consiste à la remplacer par une opération d’insertion en bloc. Il s’agit d’une opération enregistrée de manière minimale dans un modèle de récupération simple et enregistré en masse. Cela peut être fait facilement en effectuant une insertion en bloc dans une nouvelle table, puis en renommant la table en une table d’origine. Les index et la contrainte requis peuvent être créés sur une nouvelle table selon les besoins.
Le code ci-dessous montre comment la mise à jour peut être convertie en une opération d’insertion en bloc. Il faut 4 secondes pour s’exécuter.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
L’insert en vrac peut ensuite être optimisé pour augmenter les performances supplémentaires.
J’espère que cela vous aidera!!!FACEBOOK / Suivez-nous sur Twitter / Rejoignez le groupe SQL Server à la croissance la plus rapide sur FaceBook