[SQL Server] Curseurs : présentation générale

En règle générale, l’utilisation d’un curseur est vivement déconseillée pour principalement des raisons de performances, SQL Server étant optimisé pour les opérations ensemblistes, sachant que les curseurs effectuent des opérations itératives. Ce billet présente néanmoins ce qu’est concrètement un curseur, comment il fonctionne sous SQL Server et en quoi il peut avoir un impact négatif sur les performances.

Dans ce billet, nous allons surtout nous focaliser sur les curseurs de type « serveur ».

Présentation d’un curseur

Qu’est-ce qu’un curseur ?

Un curseur est un mécanisme permettant de travailler itérativement en mémoire tampon sur un jeu de résultats référençant, via des pointeurs, des données d’une base.

La syntaxe de création d’un curseur est la suivante :

DECLARE NotreCurseur CURSOR
   [ LOCAL | GLOBAL ] -- Portée
   [ FORWARD_ONLY | SCROLL ] -- Parcours
   [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] -- Type
   [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] -- Options de modification
   [ TYPE_WARNING ]
FOR
   SELECT … FROM …
   [ FOR UPDATE [ OF NomColonne [ ,...n ] ] ]
GO

Principales options de contrôle

Il existe quelques options permettant de contrôler le comportement d’un curseur :

  • Portée ou visibilité :
    • LOCAL : se limite à la session de l’utilisateur qui l’a créé.
    • GLOBAL : portée globale, donc peut être référencé par n’importe quelle session connectée à l’instance. Il s’agit de l’option par défaut.
  • Options de parcours :
    • SCROLL : permet d’indiquer que le curseur peut aller de la première à la dernière ligne, tout comme il peut effectuer le sens inverse. Est incompatible avec FAST_FORWARD (voir plus loin).
    • FORWARD_ONLY : permet d’indiquer que le curseur ne peut aller que de l’avant.
  • Options de modification :
    • READ_ONLY : permet d’interdire la mise-à-jour de tables sous-jacentes via le curseur.
    • UPDATE (OF <Nom_colonne>,…) : permet de définir la ou les colonnes des tables sous-jacentes candidates à toute modification via le curseur. Il s’agit de l’option par défaut. La clause WHERE CURRENT OF peut être utilisée pour mettre-à-jour la dernière ligne de données récupérée.
    • OPTIMISTIC : permet d’indiquer que les modifications (éditions ou suppressions) effectuées sur les données des tables sous-jacentes via le curseur seront annulées si les lignes concernées ont été mises-à-jour depuis qu’elles ont été lues dans le curseur. Est incompatible avec FAST_FORWARD (voir plus loin).
    • SCROLL_LOCKS : permet d’indiquer que les modifications (éditions ou suppressions) effectuées sur les données des tables sous-jacentes via le curseur sont garanties de réussir. Pour ce faire, SQL Server procède à la pose d’un verrou sur les lignes qui seront lues dans le curseur pour s’assurer de leur disponibilité pour une ou plusieurs modifications ultérieures.
L’option TYPE_WARNING permet au curseur d’envoyer un message d’avertissement en cas de conversion implicite d’un type spécifique vers un autre type.
Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms180169.aspx.

Processus de fonctionnement d’un curseur

Voici, ci-dessous, un exemple élémentaire de création d’un curseur (appelons-le NotreCurseur) permettant la récupération de tous les noms et prénoms des employés :

DECLARE @cNom VARCHAR(50),@cPrenom VARCHAR(50)
DECLARE NotreCurseur
CURSOR FOR
   SELECT Nom, Prenom FROM Employes OPEN NotreCurseur FETCH NEXT FROM NotreCurseur INTO @cNom, @cPrenom
   PRINT @cNom+' '+@cPrenom
WHILE @@FETCH_STATUS=0
BEGIN
   FETCH NEXT FROM NotreCurseur INTO @cNom, @cPrenom
   PRINT @cNom+' '+@cPrenom
END
CLOSE NotreCurseur
DEALLOCATE NotreCurseur
GO

L’exemple ci-dessus permet, comme indiqué, de récupérer le nom (Nom) et le prénom (Prenom) des employés (table Employes) et de les afficher. Comme on peut le noter, le processus de fonctionnement d’un curseur est le suivant :

  • Création via DECLARE et constitution du jeu de résultats avec SELECT.
  • Ouverture avec OPEN (acquisition de pages mémoire tampon pour les pointeurs du curseur et préparation du jeu de résultats).
  • Récupération des valeurs contenues dans le curseur via FETCH… INTO @cNom, @cPrenom, ces deux variables (pointeurs) préalablement déclarées permettant justement de stocker les valeurs récupérées.
  • Parcours du curseur, dans notre exemple, effectué en employant la boucle WHILE qui vérifie la condition de la fonction @@FETCH_STATUS qui renvoie 0 tant que l’on n’est pas en fin de parcours (-1, s’il n’y a plus de ligne ; -2, si ligne manquante).
  • Fermeture via CLOSE.
  • Libération de la mémoire utilisée via DEALLOCATE (à ne surtout pas oublier).

Notons que FETCH peut, au sein d’un curseur, être utilisé de diverses façons :

  • Positionnement à la première ligne : FETCH FIRST FROM NotreCurseur.
  • Positionnement à la dernière ligne : FETCH LAST FROM NotreCurseur.
  • Positionnement à la ligne suivante (comme dans notre exemple ci-dessus, et qui est le comportement par défaut de FETCH) : FETCH NEXT FROM NotreCurseur.
  • Positionnement à la ligne précédente : FETCH PRIOR FROM NotreCurseur.
  • Positionnement à une ligne n spécifique : FETCH ABSOLUTE n FROM NotreCurseur.
  • Positionnement à n lignes plus loin que l’actuelle : FETCH RELATIVE n FROM NotreCurseur.

Plus de détails sur FETCH ici : http://msdn.microsoft.com/en-us/library/ms180152.aspx.

A l’instar de @@FETCH_STATUS, d’autres fonctions de curseur peuvent être utilisées. Citons notamment :

Les différents types de curseurs

Présentation des différents types de curseurs

Il existe 4 types de curseurs :

  • FAST_FORWARD (FORWARD_ONLY + READ_ONLY) : le curseur de type « En avant uniquement » traite les données d’un jeu de résultats au fur et à mesure, en allant de l’avant (donc, seul FETCH NEXT est supporté) et sans prendre en compte toute modification éventuelle sur celles des tables sous-jacentes. Dans la mesure où ce type de curseur ne permet de naviguer que vers l’avant, il ne bloque les tables que durant une très courte durée (i.e., le temps de la mise-en-mémoire du jeu de résultats) et permet de libérer rapidement de la mémoire tampon dès la fin des traitements. Le gros désavantage, cependant, est qu’il n’empêche pas de travailler sur des données potentiellement obsolètes, aucune mise-à-jour sur les tables sous-jacentes n’étant prise en compte. Ce type de curseur est plus adapté pour le traitement rapide d’un jeu de résultats temporaire sans prise en compte de modifications sous-jacentes pour des raisons de performances.
  • STATIC : le curseur de type statique copie les données de ses jeux de résultats (en mémoire) dans tempDB. Par conséquent, les traitements dudit curseur (via FETCH) sont effectués dans tempDB, ce qui fait que
    toutes les mises-à-jour faites sur les données des tables sous-jacentes ne seront pas reflétées par le curseur. Contrairement au curseur de type FAST_FORWARD, il est possible d’explorer les données dans les deux sens (avant et arrière). Ce type de curseur est donc plus adapté pour le traitement de données statiques.
  • KEYSET : le curseur de type « Jeu de clés » fonctionne de la même façon que les curseurs statiques, à la différence qu’il ne stocke, dans tempDB, que les données contenues dans la clé primaire. De plus, l’ordre des données contenues dans le curseur est maintenu, et les traitements de récupération de données (FETCH) sont effectués sur les tables sous-jacentes via la clé de chaque donnée stockée dans tempDB. Dans la mesure où le curseur de type KEYSET « ressent » les modifications sur les tables sous-jacentes, mais ne voit pas les nouvelles lignes de données (après constitution du ou des jeux de résultats du curseur), s’il tombe sur une ligne qui n’existe plus, mais qui est référencée par une clé dans tempDB, la variable @@FETCH_STATUS aura une valeur égale à -2. Ce type de curseur permet la navigation et la prise en compte de modifications, hormis les ajouts ou suppressions de lignes.
  • DYNAMIC : le curseur de type dynamique prend également en compte les ajouts et suppressions de lignes de données sous-jacentes. Ce type de curseur permet donc la navigation et la prise en compte de tout type de modification sur les données des tables sous-jacentes. En revanche, le fait que les données, leur ordre et leur présence peuvent changer durant chaque FETCH fait de ce curseur le plus coûteux en matière de performances (blocages, I/O,…).

Tableau comparatif des différents types de curseurs

Le tableau ci-dessous offre quelques éléments de comparaison des différents types de curseurs :

FAST_FORWARD STATIC KEYSET DYNAMIC
Niveau de blocage ? Léger. Moyen. Elevé. Très élevé.
Modes de navigation possibles ? Avant seulement. Avant et arrière. Avant et arrière. Avant et arrière.
Prise en compte des mises-à-jour ? Non. Non. Oui (sauf les ajouts et suppressions de lignes). Oui.
Plus adapté pour… Traitement de données en lecture seule et/ou à usage unique. Traitement de données statiques. Traitement de données sujettes à des mises-à-jour. Traitement de données sujettes à des mises-à-jour, avec garantie de prise en compte de toutes les modifications possibles.

Avantages et inconvénients de l’utilisation d’un curseur

Les principaux avantages de l’utilisation d’un curseur sont les suivants :

  • Le scrolling (parcours de lignes de la première à la dernière, et vice-versa).
  • Les opérations ligne par ligne.

Ces avantages sont relatifs car ils peuvent être supportés via d’autres alternatives moins coûteuses en ressources (boucle WHILE, tables temporaires, CTE, ensemble de procédures stockées,…) ne rendant pas indispensable l’utilisation d’un curseur.

Dans certains scénarii très ponctuels, l’utilisation de curseurs peut être légitime. C’est le cas de tâches de maintenance, telles que la gestion de sauvegardes, la réindexation,….

Et côté inconvénients, ou plutôt là où le bât blesse… :

  • Les curseurs sont gourmands en ressources. Bien qu’un curseur de type FAST_FORWARD (d’ailleurs, quitte à utiliser un curseur, autant utiliser celui-là dans la majeure partie des cas)
    peut très souvent fournir des temps de réponse plus intéressants qu’une boucle WHILE (des tests seront effectués dans un prochain billet), il faut retenir qu’un curseur est, avant tout, un mécanisme qui utilise des pointeurs en mémoire. Cela sous-entend qu’il s’accapare des pages de mémoire qui auraient pu être utilisées par d’autres processus. Et il peut également impacter la volumétrie disque (stockage de données temporaires dans tempDB pour traitement, notamment dans le cas des curseurs de type STATIC et KEYSET).
  • Blocages. L’ouverture d’un curseur charge un (ou plusieurs) jeu(x) de résultats temporaires générant ainsi des locks sur la ou les tables sous-jacentes traitées. Cela peut provoquer de longs moments de blocages si la quantité de données à traiter est volumineuse.
  • Décalage possible entre les données traitées dans le curseur et celles des tables sous-jacentes. Si la mauvaise option est utilisée (i.e., FAST_FORWARD, STATIC), le curseur peut être amené à travailler sur des données obsolètes si, entre-temps, des mises-à-jour ont eu lieu sur les lignes de données des tables sous-jacentes. Les curseurs dynamiques, comme vu plus haut, permettent de garantir la consistance des données, mais peuvent générer beaucoup de blocages, en contrepartie, ce qui renvoie au point précédent…

Pour aller plus loin…

Dans ce billet, nous avons pu voir ce qu’est un curseur, son fonctionnement, ses inconvénients,… Dans le futur, nous présenterons différentes solutions alternatives aux curseurs.

Pour plus d’informations sur les curseurs sur MSDN : http://msdn.microsoft.com/fr-fr/library/ms180169.aspx et http://msdn.microsoft.com/en-us/library/ms187859.aspx.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s