Intro

Dans les bases de données relationnelles, les opérations sont effectuées sur un ensemble de lignes. Par exemple, une instruction SELECT renvoie un ensemble de lignes appelé ensemble de résultats. Parfois, la logique de l’application doit fonctionner avec une ligne à la fois plutôt qu’avec l’ensemble de résultats à la fois. Dans T-SQL, une façon de faire ceci est d’utiliser un CURSEUR.

Si vous possédez des compétences en programmation, vous utiliseriez probablement une boucle comme FOR ou WHILE pour parcourir un élément à la fois, faites quelque chose avec les données et le travail est terminé. Dans T-SQL, un CURSOR est une approche similaire et peut être préférable car il suit la même logique. Mais attention, prenez ce chemin et des problèmes peuvent survenir.

Il y a des cas où l’utilisation de CURSOR ne fait pas tellement de gâchis, mais en général, ils doivent être évités. Ci-dessous, nous montrerons quelques exemples où l’utilisation d’un CURSOR crée des problèmes de performances et nous verrons que le même travail peut être effectué de nombreuses autres manières.

Pour les besoins de cette démonstration, nous utiliserons la base de données AdventureWorks2012, et disons que nous voulons obtenir des données. table, pour chaque produit dont la fabrication nécessite moins d’un jour, c’est-à-dire à partir de la table ..

Un exemple de curseur

Commençons par utiliser un CURSOR, et écrivons la syntaxe suivante:

Après une courte pause-café , la requête a terminé son exécution, renvoyant 833 lignes dans le délai indiqué ci-dessous. Il est important de mentionner que les syntaxes choisies ci-dessus sont uniquement à des fins de démonstration, et je n’ai fait aucun réglage d’index pour accélérer les choses.

Dans l’exécution du curseur, nous avons deux étapes. Première étape, le positionnement, lorsque le curseur définit sa position sur une ligne du jeu de résultats. Deuxième étape, la récupération, lorsqu’elle obtient les données de cette ligne spécifique dans une opération appelée FETCH.

Dans notre exemple, le curseur définit sa position sur la première ligne retournée par le premier SELECT et récupère la valeur ProductID qui correspond à la condition WHERE dans la variable @id. Ensuite, le deuxième SELECT utilise la valeur de la variable pour obtenir des données. et la ligne suivante est récupérée. Ces opérations sont répétées jusqu’à ce qu’il n’y ait plus de lignes avec lesquelles travailler.

Enfin, la syntaxe CLOSE libère le jeu de résultats actuel et supprime les verrous des lignes utilisées par le curseur, et DEALLOCATE supprime la référence du curseur.

Nos tables de démonstration sont relativement petites contenant environ 1 000 et 500 lignes. Si nous devions parcourir des tables avec des millions de lignes, cela durerait un temps considérable et les résultats ne nous plairaient pas.

Donnons à notre curseur une autre chance et décommentons la ligne –LOCAL STATIC. Il y a de nombreux arguments que nous pouvons utiliser dans une définition de curseur, plus à ce sujet sur ce lien CURSOR Arguments, mais pour l’instant, concentrons-nous sur ce que signifient ces deux mots.

Lorsque nous spécifions le mot-clé LOCAL, la portée du curseur est locale au lot dans lequel il a été créé et elle n’est valide que dans cette portée. Une fois l’exécution du lot terminée, le curseur est automatiquement désalloué. De plus, le curseur peut être référencé par une procédure stockée, un déclencheur ou par une variable de curseur locale dans un batch.

Le mot clé STATIC fait une copie temporaire des données utilisées par le curseur dans tempdb dans une table temporaire . Ici, nous avons quelques pièges. Un curseur STATIC est en lecture seule et est également appelé curseur d’instantané car il ne fonctionne qu’avec les données du moment où il a été ouvert, ce qui signifie qu’il n’affichera aucune modification apportée dans la base de données sur l’ensemble de données utilisé par le le curseur.Fondamentalement, aucune mise à jour, suppression ou insertion effectuée après l’ouverture du curseur ne sera visible dans le jeu de résultats des curseurs, sauf si nous fermons et rouvrons le curseur.

Soyez conscient de cela avant d’utiliser ces arguments et vérifiez si cela correspond à vos besoins. Mais voyons si notre curseur est plus rapide. Ci-dessous, nous avons les résultats:

12 secondes valent bien mieux que 4 minutes et 47 secondes, mais gardez à l’esprit que restrictions expliquées ci-dessus.

Si nous exécutons la syntaxe en utilisant cet argument LOCAL READ_ONLY FORWARD_ONLY, nous obtenons les mêmes résultats. Le curseur READ_ONLY FORWARD_ONLY ne peut être déplacé que de la première ligne à la dernière. Si le mot clé STATIC est manquant, le curseur est dynamique et utilise un plan dynamique s’il est disponible. Mais il y a des cas où un plan dynamique est pire qu’un plan statique.

Que se passe-t-il lorsque nous décommentons –LOCAL FAST_FORWARD?

FAST_FORWARD équivaut aux curseurs READ_ONLY et FORWARD_ONLY mais a la possibilité de choisir le meilleur plan entre un plan statique ou dynamique.

LOCAL FAST_FORWARD semble être le meilleur choix en raison de sa flexibilité pour choisissez entre un plan statique ou dynamique, mais FORWARD_ONLY fait également très bien le travail. Bien que nous ayons obtenu le résultat en 12 secondes en moyenne en utilisant ces deux méthodes, ces arguments doivent être correctement testés avant de choisir l’un d’eux.

Il existe de nombreuses façons d’obtenir le même résultat, beaucoup plus rapidement et avec moins d’impact sur les performances.

Alternative au curseur

Une méthode utilise un JOIN, et comme nous pouvons le voir ci-après, les résultats sont nettement meilleurs.

Premièrement, nous devons activer le temps de statistiques pour mesurer le temps d’exécution de SQL Server, et faire un INNER JOIN entre deux tables,. et . sur la colonne ProductID. Après avoir appuyé sur le bouton EXECUTER, nous avons produit les mêmes résultats en 330 ms, contre 04:47 avec la méthode du curseur, et nous avons un sourire sur notre visage.

Nous n’avons pas besoin de parcourir chaque ligne pour obtenir ce dont nous avons besoin, nous n’avons plus de boucles, pas de clause while, pas d’itération ations, nous travaillons avec des ensembles de données à la place, obtenant ce que nous voulons plus rapidement et écrivant moins de code.

Une utilisation appropriée du curseur

Maintenant que nous avons vu combien de dégâts un curseur peut faire, voyons un exemple où nous pouvons l’utiliser.

Supposons que nous voulons sélectionner la taille et le nombre de lignes pour certaines tables seulement d’une base de données. Pour ce faire, nous obtiendrons tous les noms de table en fonction des critères de information_schema.tables et en utilisant un CURSOR, nous bouclerons chacun de ces noms de table et exécuterons la procédure stockée sp_spaceused en passant un nom de table à la fois pour obtenir les informations dont nous avons besoin .

Nous utiliserons la même base de données AdventureWorks2012, et obtiendrons toutes les tables du schéma Sales qui contient le nom «Sales». Pour chaque nom de table renvoyé, nous voulons voir toutes les informations de information_schema.tables.

Ci-dessous, nous avons la syntaxe T-SQL et les résultats obtenus:

C’est une méthode où CURSOR est utile en itérant à travers certaines données une ligne à la fois et obtient le résultat nécessaire. Dans ce cas particulier, le curseur fait le travail sans avoir d’incidence sur les performances et est facile à utiliser.

/ p>

Conclusions

Voilà. Nous avons montré quelques exemples avec le bon, le mauvais et le laid lors de l’utilisation de curseurs. Dans la plupart des cas, nous pouvons utiliser JOINS, même des clauses WHILE, SSIS packages ou d’autres méthodes alternatives pour obtenir le même résultat plus rapidement, avec moins d’impact sur les performances de sortie et même en écrivant moins de lignes de syntaxe.

Lorsque nous traitent d’un environnement OLTP et de grands ensembles de données à traiter, le mot ‘CURSOR’ ne doit pas être prononcé.

En SQL, il est préférable de penser à effectuer des opérations sur des ensembles de données, plutôt que de penser de manière programmatique, en utilisant des itérations ou des boucles, car ce type d’approche n’est ni recommandé ni destiné à cet usage. Essayer d’utiliser des boucles comme FOR ou FOREACH à partir de langages de programmation et associer cette logique aux opérations SQL, est un obstacle pour trouver la bonne solution à nos besoins. Nous devons penser à des opérations basées sur des ensembles plutôt qu’à une ligne à la fois pour obtenir les données dont nous avons besoin.

Les curseurs pourraient être utilisés dans certaines applications pour des opérations sérialisées comme indiqué dans l’exemple ci-dessus, mais en général, ils devraient à éviter car ils ont un impact négatif sur les performances, en particulier lors de l’utilisation de grands ensembles de données.

• Auteur
• Messages récents

Je suis DBA chez Yazaki Component Technology avec 3 ans d’expérience dans SQL Server.
J’ai un baccalauréat en génie informatique et possède également des compétences en .NET, C # et Windows Server. J’ai développé une expérience dans l’optimisation et la surveillance des performances, le développement T-SQL, la stratégie de sauvegarde, l’administration et la configuration de bases de données.
Je suis passionné par l’informatique, les jeux vidéo, les bons films et la musique électro-house qui m’aide à travailler lorsque le bureau est bruyant.
Afficher tous les messages de Sergiu Onet

Derniers articles de Sergiu Onet (tout voir)

• Utilisation des curseurs SQL Server – Avantages et inconvénients – 23 mars 2016