Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Quand faut il utiliser les curseurs ? Et ne pas les utiliser ?

C'est une question récurrente, et quelques mythes existent autour de ce thème. Tout comme j'avais exposé ce fait pour le SQL dynamique (http://blogs.codes-sources.com/christian/archive/2009/07/13/sql-server-pourquoi-utiliser-du-sql-dynamique-cha-ne-concat-n-e-contenant-du-sql-et-pourquoi-l-interdire.aspx).

Tout d'abord, plongeons nous dans le rôle et le fonctionnement du curseur. Ce dernier permet de boucler ou itérer sur un jeu de résultat. Il existe 2 principaux types de curseurs, les curseurs « client » et les curseurs « serveur ».

Côté client c'est la couche d'accès aux données qui va lister les contenu de votre jeu de résultat et vous renvoyer les lignes, en .Net un sqlDataReader avec sa méthode .Read() agit comme un itérateur, il s'agit ni plus ni moins que d'un curseur « client ». Le moteur de base de données n'est pas impliqué dans cette opération, il se contente d'envoyer les résultats bruts et le client décode le résultat sous formes de lignes distinctes. Les curseurs client sont utilisés de manière transparente et ne posent généralement pas de problèmes, à quelques exceptions près.

Côté client en effet l'on va récupérer les données du serveur, mais encore faut il avoir assez de mémoire pour mettre en cache le jeu de données envoyé par le serveur. Si cette mémoire n'existe pas ou est limité, c'est là que le code de récupération va se trouver critique. Dans de rares cas le moteur de bases de données va se trouver à attendre sur le client, pour que ce dernier récupère ses données actuellement en cache sur le serveur. De plus entre le client est le serveur se trouve généralement un réseau qui lui aussi est facteur de ralentissement, d'ailleurs que ce soit le client ou le réseau qui est en cause SQL Server signalera une attente identique : ASYNC_NETWORK_IO.

En le comprend assez vite, en ce qui concerne les curseurs client, l'ennemie est le volume de données à traiter. C'est encore plus vrai quand il vous faut trier ces même données du côté du client, cela va engendre une consommation excessive de temps processeur ! Donc mémoire tampon suffisante côté client et faible volume de données sont les maîtres mots de ce type de curseurs.

Côté serveur c'est une tout autre affaire, les types de curseurs sont nombreux et leurs comportements très différent de l'un à l'autre.

  • Static - Statique
  • Dynamic - Dynamique
  • Forward-only - En avant uniquement
  • Keyset - Jeu de clefs

Celui qui se rapproche le plus du curseur côté client, est le « Forward-only », celui-ci décode au fur et à mesure le flux de données mis en tampon et renvoie les différente lignes. Par contre tout comme côté client, les données en court de traitement ne sont peut être plus celles actuellement présentes dans les tables, la navigation se fait en générale que vers l'avant (ce qui permet de libérer rapidement la zone de mémoire tampon). L'avantage de ce type de curseurs c'est justement son faible impact, les tables sont bloquées pendant une durée très courte le temps de la mise en mémoire tapon du jeu de résultat.

Les curseurs Statiques et KeySet, ont en commun la mise de côté d'une partie des données dans tempdb. Le premier y stocke la totalité du jeu de données, le second seulement les données contenues dans la clef primaire. Les 2 permettent une exploration dans les 2 sens (avant et arrière) des données. Ici aussi l'impact est faible sur les données sources dans la mesure où seule la lecture et la mise en cache dans tempdb est bloquante. Le curseur statique a presque les même limite que le « En avant uniquement » en ce qui concerne le décalage entre les données en cache et celle actuellement dans les tables, les mises à jours à partir des données présente dans le curseur sont donc assez déconseillée. Mais comme les données sont dans tempdb et non plus en mémoire le volume de données traité peut être plus important, attention cependant aux très gros volumes de données, particulière si vous n'utilisez pas toutes les colonnes.

Dans le cas du curseur KeySet, seules la clef primaire est mise en cache dans tempdb et la navigation se fait grâce à celle-ci. Cette fois les mises à jour et la navigation sont possibles sans trop de problèmes. Il faudra juste faire attention à la suppression ou l'ajout d'enregistrements qui ne seront pas reflétés par ce type de curseurs (le premier signalera l'absence de la ligne, tandis que le second cas l'enregistrement ne sera connu qu'à l'exécution d'un nouveau curseur). Le principal défaut de ce curseur est de devoir exécuter à chaque itération une requête équivalente à :

SELECT * FROM dbo.MaTable WHERE Clef = x

Ce qui, verrouille la ligne et surtout oblige le moteur à lire au travers de l'index de la clef primaire. Du coup lire plus de 1% (et c'est déjà beaucoup, en réalité plus 0,5% ou 0,1%) des données de la table au travers de ce curseur multiplie par 3 ou 4 le charge d'IO (lectures essentiellement) qu'un simple SELECT * sur cette même table !

Le dernier type (curseur dynamique), permet quant à lui tout type d'accès et reflète exactement l'état des données. Mais cela à bien entendu un coup en termes de verrouillage. Il n'y a pas de secret pour être certains d'avoir sous la main l'ensemble des données des tables sous-jacente il faut bien signaler au moteur ce que l'on fait et empêcher un certains nombre d'opération. Les accès à chaque ligne se révèlent aussi gourmant.

Un petit tableau pour résumer :

 

Forward-only

Static

Keyset

Dynamic

Bloquant

+

++

+++

++++

Décalage entre les données du curseur et celle des tables

Potentiellement Important

Potentiellement Important

Seul les Insertions et Suppressions de ligne

Aucuns

Navigation

Avant

Avant et arrière

Avant et arrière

Avant et arrière

Mise à jour

Non

Non

Oui

Oui

Impact en termes de lecture / écriture

Nul

Copie des données

Copie de la clef primaire plus accès à chaque ligne

Accès à chaque ligne

Usage ?

Lecture d'un jeu de données à usage unique

Navigation dans un jeu de données à usage multiple

Navigation et mises à jour sur une faible portion de données

Navigation et mises à jour sur une faible portion de données, avec garantie des données à jour

 

Une fois connue les différents types de données, reste à en connaître l'usage. Comme dit plus haut c'est un itérateur qui va boucler sur chacune des lignes renvoyés par une requête, mais quand est ce que nous avons besoins de cela ?

Côté client c'est simple cela permet par exemple de construire un tableau et d'y placer les données. Du côté serveur c'est plus difficile à défendre, car partir dans une logique de boucle, qui plus est, piloté par du code maison c'est aussi interdire les optimisations du moteur de base de données.

Prenons un exemple (volontairement exagéré), si je souhaite réaliser une jointure entre 2 tables.

En raisonnement « ensembliste », en gros avec une requête SQL :

SELECT *

FROM dbo.MaTableA AS A

    JOIN dbo.MaTableB AS B ON A.Id = B.Id

En raisonnement « itératif », avec un curseur :

DECLARE @ID int ;

DECLARE crsTest CURSOR FOR SELECT Id FROM dbo.MaTableA ;

 

OPEN crsTest ;

 

FETCH NEXT FROM crsTest INTO @ID ;

 

WHILE ( @@FETCH_STATUS != 0)

BEGIN

 

    SELECT * FROM dbo.MaTableB AS B WHERE B.Id = @ID

 

    FETCH NEXT FROM crsTest INTO @ID ;

 

END

 

CLOSE crsTest ;

DEALLOCATE crsTest ;

Dans le premier cas, SQL Server a le choix de la méthode à utiliser pour la jointure. En l'occurrence en fonction de la taille des 2 tables et d'autres critères (la cardinalité entre autre qui est le nombre de lignes correspondant dans B à une ligne dans A, exemple combien de commande a en moyenne un client) il pourra réaliser une boucle, réaliser une fusion ou créer une table hachage. De plus dans le cas de la boucle, le moteur de base de données choisira de boucler sur la plus petite table (celle qui après le WHERE est estimé avoir le moins de lignes à traiter).

Dans le second cas, on est bloqué sur une boucle, pas d'autres choix et si vous n'avez pas de chance, celle-ci va se faire sur un grand volume de données. En effet à chaque ligne de A, je vais lire au moins une ligne de B et faire au moins un accès d'index dans B… Dans le pire des cas vous lirez 3 à 4 fois le contenu de la table B (1 accès d'index c'est généralement 3 à 4 pages à lire, vous multipliez par le nombre de ligne…) Si votre table est très volumineuse, bonjour les dégâts !

Comme je le disais plus haut ce scénario est volontairement exagéré, mais illustre ce qui est bien souvent fait à l'aide de curseurs directement ou indirectement. Cela pénalise très fortement les performances… même si je reconnais volontiers que ce code est plus simple à comprendre que le comportement interne d'une jointure ! D'ailleurs la force de LINQ to SQL ou to Entities, n'est elle pas de transformer des curseurs en requête à base de jointure ?

Dans quels cas les curseurs sont utiles et peu ou pas remplaçable ?

J'en ai au moins 2 à l'esprit…

Premier scénario, j'ai une procédure stockée qui me permet de calculer des taxes pour une commande. Celle-ci est généralement appelé à chaque validation de commande et met à jour la table commande avec le bon montant et recalcule le total. Un jour vous découvrez un bug dans cette dernière, vous corrigez et… Eh bien, d'après vous, comment j'exécute cette procédure stockée pour chaque ligne dans ma table commande ??? Un curseur sur la table commande et dans chaque itération j'appelle ma procédure corrigée ! Oui, avoir une fonction dans ce scénario aurait été un plus, mais dans la mesure où la procédure fait elle-même les mises à jour je vous laisse imaginer le casse tête de la transformation du code.

Deuxième scénario, vous souhaitez réaliser une somme cumulée du montant des commandes sur chaque commande. Par exemple, ma commande n° 25 478 de 567 € reprendra la somme des 25477 précédente commande + 567 € dans la colonne cumul. Si on réalise ce calcul après coup, c'est-à-dire si j'ajoute cumul comme nouvelle colonne à ma table de commande et que je décide de calculer cette valeur il va falloir définir l'ordre dans lequel je le réalise (par exemple le numéro de commande, la date, etc.) et surtout écrire la requête ! Dans cet exemple, la requête la plus rapide et la plus compréhensible consiste à itérer sur les commandes triées dans l'ordre souhaité faire la somme dans une variable, et mettre à jour chaque ligne depuis le curseur. Essayez la même chose sans curseurs et vous comprendrez ! (Certains moteur fournissent le SUM() OVER(ORDER BY xxx), pour le moment cette fonctionnalité n'est pas implémentée dans SQL Server).

J'espère qu'après ce long article vous aurez moins d'aprioris sur les curseurs, en tout cas, surtout que vous en connaissiez bien les limites et qu'avant d'en utiliser, vous vous posiez une question : Est il possible de le faire autrement facilement ?

Bonne boucle…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié mercredi 9 décembre 2009 10:44 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- [ #Yammer ] From Mailbox to Yammer and back / De votre messagerie vers Yammer et retour ! par Le blog de Patrick [MVP SharePoint] le 09-15-2014, 11:31

- [ #Office 365 ] New service settings panel / Nouveau panneau de paramétrage des services par Le blog de Patrick [MVP SharePoint] le 09-11-2014, 08:50

- Problème de déploiement pour une démo SharePoint/TFS? par Blog de Jérémy Jeanson le 09-10-2014, 21:52

- [ #Office365 ] Delve first impressions / Premières impressions sur Delve par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 16:57

- [ #Office365 ] How to change Administration console language ? / Comment changer la langue de la console d’administration ? par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 08:25

- [ #SharePoint 2013 ] Suppression de bases de données en état “Pas de Réponse” par Le blog de Patrick [MVP SharePoint] le 09-04-2014, 14:10

- Changer l’adresse d’une ferme Office Web Apps associée à SharePoint par Blog de Jérémy Jeanson le 09-01-2014, 22:21

- Une ferme #SharePoint 2013 dans @Azure en quelques clics (1ère partie) ! par Le blog de Patrick [MVP SharePoint] le 08-28-2014, 18:52

- SharePoint 2013: Préparation de la migration - Création des site Templates dans 2010 et 2013 par Blog Technique de Romelard Fabrice le 08-20-2014, 16:31

- [ #Yammer ] How to change interface language ? Comment changer la langue de l’interface ? par Le blog de Patrick [MVP SharePoint] le 08-20-2014, 14:21