Les curseurs peuvent ressembler à des raccourcis vers un développeur. Lorsque vous avez un travail complexe à effectuer et que vous devez manipuler les lignes d’une table, le moyen le plus rapide peut sembler d’itérer les lignes une par une à l’aide d’un curseur Transact-SQL. Après tout, puisque vous devez parcourir les structures de données dans votre propre code côté client, vous pouvez être tenté de faire de même lorsque vous traitez des données SQL Server., Mais itérer à travers les données à l’aide de curseurs Transact-SQL ne fonctionne souvent pas bien, et j’espère vous convaincre que ce n’est pas non plus une bonne pratique de conception ou d’architecture.

une expérience de curseur

j’en parle car il y a quelques mois, j’ai dû faire face au script Transact-SQL d’un fournisseur qui a mis à niveau son composant de base de données vers une nouvelle version de l’application du fournisseur. Ils ont conçu le script pour faire pivoter une très grande table et stocker les données pertinentes dans une nouvelle table horizontalement, sous forme de chaînes concaténées., Le fournisseur voulait améliorer les performances en rendant la table plus petite, il a donc décidé de stocker les données détaillées horizontalement, sous forme de chaînes séparées par des virgules pour chaque id parent. L’application cliente pourrait interroger les chaînes délimitées par des virgules résultantes plus rapidement que d’obtenir chacune d’elles en tant que lignes individuelles, et dans le contexte, le changement était logique et améliorait les performances de l’application.

cependant, le script Transact-SQL du fournisseur pour faire pivoter les données pendant la mise à niveau a pris 16 heures pour s’exécuter sur une machine de test, et le client ne pouvait pas se permettre plus de quelques heures de temps d’arrêt pour la mise à niveau., Lorsque nous avons examiné le script du fournisseur, nous avons vu que le développeur avait codé le processus de pivotement en deux étapes: un curseur pour parcourir tous les ID de table parent pour construire une table pré-formatée vierge, puis un autre script pour concaténer les chaînes, à nouveau en utilisant un curseur.

en utilisant une approche définie, nous avons pu réduire le temps de traitement de plus de 16 heures à moins de cinq minutes. Nous avons suivi la stratégie originale du développeur, en construisant le tableau vierge à l’aide D’instructions SELECT, et nous avons réduit le temps de cette étape à moins de deux minutes., Nous avons ensuite concaténé les chaînes à l’aide d’une instruction UPDATE, exécutée par id parent. Notre itération à travers les ID parents a utilisé une boucle WHILE et s’est terminée en moins de trois minutes.

l’Inévitabilité de L’Itération

de Nombreux accès à la base de données doit être itératif, d’une certaine manière afin de préparer les données pour la manipulation. Même le moteur SQL Server parcourt les données lorsqu’il scanne ou joint des données à l’aide des différents types de jointures disponibles. Vous pouvez le voir lorsque vous examinez le plan de requête SQL Server pour une requête qui renvoie de nombreuses lignes à partir d’un ensemble de données volumineux., Pour une jointure, vous verrez le plus souvent une boucle imbriquée, mais parfois aussi une jointure de fusion ou de hachage. Pour des requêtes plus simples, vous pouvez voir une analyse d’index en cluster ou non en cluster. Ce n’est que dans les cas où SQL Server peut renvoyer une seule ligne ou un petit ensemble de lignes, et la table a un index approprié, que vous verrez une recherche à l’aide d’un index.

pensez-y: Microsoft a optimisé et réglé le moteur SQL Server pendant des années pour parcourir ses données disponibles aussi efficacement que possible., Imaginez, si vous aviez le temps et étiez prêt à dépenser l’énergie, vous pourriez probablement écrire des accès de bas niveau aux fichiers de données de base de données qui seraient assez efficaces. Cependant, il ne serait efficace que pour la tâche individuelle devant vous, et vous devrez la déboguer et la réécrire complètement si la portée de votre accès aux données devait changer. Il vous faudrait probablement des années pour vraiment obtenir le code entièrement optimisé et généralisé, et même dans ce cas, vous ne seriez pas proche de l’efficacité du code dans le moteur de Stockage SQL Server.,

alors, où est le gain à réinventer la roue? C’est juste parce que le moteur SQL Server est si bien optimisé et débogué, qu’il est préférable de le laisser faire l’itération pour vous et de profiter du développement et des tests approfondis qui sont déjà intégrés dans la base de données.

Si vous regardez vos tâches de traitement de données de plus près, je pense que vous constaterez qu’il y a vraiment très peu d’occasions où des curseurs sont nécessaires. Tout d’abord, vous pouvez souvent atteindre votre objectif en vous appuyant sur les commandes SQL basées sur set dans Transact-SQL et en ignorant l’ordre des lignes d’une table., Deuxièmement, les curseurs Transact-SQL ne sont qu’un moyen d’itérer dans une table ligne par ligne. Si vous pouvez identifier de manière unique chaque ligne d’une table que vous devez itérer, vous pouvez utiliser une boucle WHILE plutôt qu’un curseur et potentiellement obtenir de meilleures performances. Laissez-moi vous guider à travers un exemple pour vous montrer pourquoi.

comparaison des stratégies D’itération

supposons que vous puissiez identifier de manière unique chaque ligne d’une table car la table a une clé unique ou un groupe unique de colonnes., Dans une boucle WHILE, tout ce que vous devez faire est de trouver la valeur la plus basse de la condition unique, puis de trouver la valeur la plus élevée suivante chaque fois que vous itérez. Voici un exemple de SQL Server 2005 AdventureWorks exemple de production de bases de données.Tableau TransactionHistory. Il a un index en cluster sur la clé primaire, et la boucle WHILE peut rechercher dans la ligne à chaque fois.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Voici la même boucle à l’aide d’un curseur D’avance rapide, qui est le type de curseur Transact-SQL le plus efficace pour lire simplement les données:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

sur mon ordinateur portable, après l’avoir exécuté plusieurs fois pour s’assurer que les données sont Vos propres durées peuvent varier. Notez que même si l’exemple ne fait vraiment rien avec les données, la boucle WHILE est plus rapide. Le curseur ajoute évidemment plus de frais généraux.,

le curseur nécessite également des commandes supplémentaires, ce qui rend le code encombré. Sans entrer dans les détails du fonctionnement des curseurs, que Microsoft explique pleinement dans Microsoft SQL Server 2005 Books Online, notez que lorsque vous utilisez une boucle WHILE, il n’est pas nécessaire de déclarer, ouvrir, fermer et désallouer quoi que ce soit. La logique est plus simple et vous pouvez même mettre à jour les lignes librement en cours de route. Pour mettre à jour les lignes à l’aide du curseur, vous devrez changer le type de curseur.

même une boucle WHILE ajoute la surcharge de l’itération., Vous pourrez peut-être le remplacer par une commande SELECT basée sur set, ou remplacer toutes les mises à jour que vous souhaitez effectuer dans votre boucle par la commande UPDATE basée sur set, et laisser l’itération au moteur SQL Server. Une simple instruction SELECT pour obtenir les mêmes données que notre curseur et WHILE loop ci-dessus prend moins de 3 secondes, et elle renvoie les lignes au client, ce qui est plus de travail que les deux boucles précédentes.

SELECT *FROM Production.TransactionHistory

Cette sélection repose sur SQL Server pour parcourir les données, et est de loin la plus rapide des trois méthodes d’accès aux données que nous avons examinées.,

des sacs aux ensembles

parfois, des curseurs peuvent sembler nécessaires. Lorsque vous devez simplement parcourir la base de données, ligne par ligne, dans leur ordre physique, parfois seulement un curseur de travail. Cela se produit le plus souvent lorsque vous avez des lignes en double et qu’il n’y a aucun moyen d’identifier de manière unique une ligne donnée dans la table. Ces tables sont des sacs, pas des ensembles, de données, car un  » sac  » n’élimine pas les valeurs en double, comme le fait un ensemble.

de tels sacs de données se produisent généralement lorsque vous importez des données à partir d’une source externe et que vous ne pouvez pas faire entièrement confiance aux données., Par exemple, si notre table D’Historique des transactions AdventureWorks n’avait pas de groupe de colonnes que vous pourriez appeler uniques et/ou avait des lignes en double, vous pourriez penser que vous devez utiliser un curseur.

Cependant, vous pouvez toujours transformer un sac de lignes dans une table normalisée. Même si vous avez des lignes en double dans une table, ou aucun ensemble de colonnes sur lesquelles vous pouvez compter pour l’unicité, vous pouvez ajouter une colonne d’identité à la table et commencer l’identité pour commencer à numéroter avec 1. Cela ajoute une clé unique à la table, vous permettant d’utiliser une boucle WHILE au lieu d’un curseur., Une fois que vous avez une clé unique, vous pouvez supprimer les doublons à l’aide de la commande de mise à jour basée sur Transact-SQL.

L’API logique pour les données de base de données

En utilisant des opérations set-base est préférable à l’itération des données vous-même d’au moins deux manières.

Tout d’abord, les commandes SQL basées sur set sont plus efficaces car vous utilisez le moteur hautement optimisé de SQL Server pour effectuer votre itération. Si vous parcourez vous-même les données, vous n’utilisez pas le moteur de Stockage SQL Server de manière optimale. Au lieu de cela, vous le parsemez de commandes pour récupérer une seule ligne à la fois., Chaque fois que vous demandez une seule ligne, votre commande doit passer par L’optimiseur SQL Server avant de pouvoir accéder au moteur de stockage, et vous finissez par ne pas utiliser le code optimisé du moteur de Stockage SQL Server. Si vous vous êtes itéré, vous comptez également sur des informations physiques étrangères sur la table, à savoir l’ordre des lignes, lors du traitement des données. Les commandes set-Base Transact-SQL SELECT, UPDATE et DELETE vous permettent d’ignorer l’ordre des lignes et de les affecter simplement en fonction des caractéristiques des données-et elles sont plus rapides.,

Deuxièmement, les commandes basées sur les ensembles sont plus logiques car penser aux données dans les ensembles vous éloigne des détails étrangers qui sont plus préoccupés par la façon dont les données sont réellement ordonnées. En fait, les commandes basées sur des ensembles comme SELECT, UPDATE et DELETE, lorsqu’elles sont appliquées directement aux tables et non dans un curseur ou une boucle WHILE, vous rapprochent logiquement de vos données, précisément parce que vous pouvez ignorer l’ordre des données.,

Voici une autre façon de penser à ce deuxième point-tout comme les procédures stockées sont L’API la plus naturelle pour que les applications s’interfacent avec SQL Server par programme, les commandes SQL basées sur des ensembles sont donc L’API appropriée pour accéder aux données relationnelles. Les procédures stockées découplent votre application des internes de base de données, et elles sont plus efficaces que les requêtes ad hoc. De même, les commandes SQL set-base à L’intérieur de Transact-SQL vous donnent une interface logique à vos données relationnelles, et elles sont plus efficaces car vous comptez sur le moteur de Stockage SQL Server pour itérer les données.,

l’essentiel n’est pas que l’itération à travers les données est mauvaise. En fait, il est souvent inévitable. Au contraire, le point est, laissez le moteur de stockage le faire pour vous et comptez plutôt sur l’interface logique des commandes Transact-SQL basées sur set. Je pense que vous trouverez peu ou pas de situations où vous devez réellement utiliser un curseur Transact-SQL.