Éviter les curseurs sous Microsoft SQL Server

Les curseurs permettent de réaliser des traitements itératifs sur des jeux de résultats. Pour autant ils possèdent de nombreux inconvénients. Ils sont notamment gourmands en ressources et instable par nature… À travers les nombreux audits de bases de données que j'ai faites, mon constat est simple : 90 % des curseurs utilisés dans les développements ne sont pas nécessaires ! Il est donc possible de les éviter la plupart du temps. Mais comment s'en passer ? Nous allons le voir à travers trois exemples, et deux procédures stockées non documentées de Microsoft…

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Remplacer un curseur par une requête

Voici un cas classique : comment réaliser un cumul de valeurs ligne à ligne. Donnons-nous un jeu d'essai simple mais coriace. Celui d'une partie de scrabble avec 3 joueurs, dont la table des scores est réalisée comme suit :

 
Sélectionnez
CREATE TABLE T_SCORE_SCR
(SCR_TOUR INT NOT NULL, 
JOR_ID INT NOT NULL,
SCR_SCORE INT NOT NULL,
CONSTRAINT PK_SCR PRIMARY KEY (SCR_TOUR, JOR_ID))

INSERT INTO T_SCORE_SCR VALUES (1, 1, 32)
INSERT INTO T_SCORE_SCR VALUES (1, 2, 19)
INSERT INTO T_SCORE_SCR VALUES (1, 3, 27)
INSERT INTO T_SCORE_SCR VALUES (2, 1, 85)
INSERT INTO T_SCORE_SCR VALUES (2, 2, 18)
INSERT INTO T_SCORE_SCR VALUES (2, 3, 21)
INSERT INTO T_SCORE_SCR VALUES (3, 1, 41)
INSERT INTO T_SCORE_SCR VALUES (3, 2, 76)
INSERT INTO T_SCORE_SCR VALUES (3, 3, 27)

Pour réaliser le cumul, il vient à l'esprit d'ordonner le jeu de résultat dans l'ordre des tours et des participants et de prévoir autant de compteurs que de joueurs (au scrabble, la limite est de quatre) :

 
Sélectionnez
CREATE TABLE #CUMULS (SCR_TOUR INT, JOR_ID INT, SCR_SCORE INT)

DECLARE C_SCORE CURSOR 
FOR   
   SELECT SCR_TOUR, JOR_ID, SCR_SCORE
   FROM   T_SCORE_SCR   
   ORDER  BY SCR_TOUR, JOR_ID
OPEN C_SCORE
DECLARE @T INT, @J INT, @S INT, @J1 INT, @J2 INT, @J3 INT
SELECT @J1 = 0, @J2 = 0, @J3 = 0
FETCH C_SCORE INTO @T, @J, @S
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @J = 1
   BEGIN
      SET @J1 = @J1 + @S
      INSERT INTO #CUMULS VALUES (@T, @J, @J1)
   END
   IF @J = 2
   BEGIN
      SET @J2 = @J2 + @S
      INSERT INTO #CUMULS VALUES (@T, @J, @J2)
   END
   IF @J = 3
   BEGIN
      SET @J3 = @J3 + @S
      INSERT INTO #CUMULS VALUES (@T, @J, @J3)
   END
   FETCH C_SCORE INTO @T, @J, @S
END
CLOSE C_SCORE
DEALLOCATE C_SCORE
SELECT * FROM #CUMULS ORDER BY 1

Il est parfaitement possible de remplacer un tel traitement à l'aide d'une requête SQL tout à fait classique. Voici une telle requête :

 
Sélectionnez
SELECT T1.SCR_TOUR, T1.JOR_ID, SUM(T2.SCR_SCORE) CUMUL
FROM   T_SCORE_SCR T1
       LEFT OUTER JOIN T_SCORE_SCR T2
            ON T1.SCR_TOUR >= T2.SCR_TOUR
               AND T1.JOR_ID = T2.JOR_ID
GROUP  BY T1.SCR_TOUR, T1.JOR_ID
ORDER  BY T1.SCR_TOUR, T1.JOR_ID 

SCR_TOUR    JOR_ID      CUMUL       
----------- ----------- ----------- 
1           1           32
1           2           19
1           3           27
2           1           117
2           2           37
2           3           48
3           1           158
3           2           113
3           3           75

Elle propose de joindre la table sur elle même par une inéqui jointure afin de faire le cumul.

Pensez donc à utiliser toutes les techniques de requêtes, comme les jointures non équi, les sous requêtes et particulièrement celles corrélées. Une requête, même très complexe sera la plupart du temps plus efficace qu'un traitement itératif à l'aide d'une boucle par curseur. Cela est dû à l'optimiseur.

II. Remplacer un curseur par une requête avec paramètre

Avec les données suivantes…

 
Sélectionnez
La table T_JOUEUR est composée de la sorte : 
CREATE TABLE T_JOUEUR_JOR
(JOR_ID     INT NOT NULL PRIMARY KEY,
 JOR_PRENOM VARCHAR(12))
INSERT INTO T_JOUEUR_JOR VALUES (1, 'Marcel')
INSERT INTO T_JOUEUR_JOR VALUES (2, 'Gérard')
INSERT INTO T_JOUEUR_JOR VALUES (3, 'Victor')
INSERT INTO T_JOUEUR_JOR VALUES (4, NULL)
INSERT INTO T_JOUEUR_JOR VALUES (5, 'Eugène')
INSERT INTO T_JOUEUR_JOR VALUES (6, 'Didier')

…nous aimerions obtenir la liste concaténée des prénoms, par exemple : Marcel, Gérard, Victor, Eugène, Didier

La solution classique utilisant un curseur est la suivante :

 
Sélectionnez
DECLARE C_PRENOM CURSOR 
FOR 
   SELECT JOR_PRENOM
   FROM   T_JOUEUR_JOR
OPEN C_PRENOM
DECLARE @UN_PRENOM VARCHAR(12)
DECLARE @ALL_PRENOMS VARCHAR(8000)
SET @ALL_PRENOMS = ''
FETCH C_PRENOM INTO @UN_PRENOM
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @UN_PRENOM IS NOT NULL
      SET @ALL_PRENOMS = @ALL_PRENOMS + @UN_PRENOM + ', '
   FETCH C_PRENOM INTO @UN_PRENOM
END
CLOSE C_PRENOM
DEALLOCATE C_PRENOM 
IF @ALL_PRENOMS IS NOT NULL
   SET @ALL_PRENOMS = SUBSTRING(@ALL_PRENOMS, 1, LEN(@ALL_PRENOMS) - 1)
SELECT @ALL_PRENOMS

Mais il est parfaitement possible de ne pas utiliser de curseur pour obtenir un tel résultat. En effet, il suffit de se rappeler qu'un simple SELECT parcoure toutes les lignes de la table. Dès lors, on peut utiliser un SELECT d'affectation avec une variable pour empiler les prénoms à la queue leu leu. Voici un tel code :

 
Sélectionnez
-- déclaration de la variable d'empilement des prénoms
DECLARE @ALL_PRENOMS VARCHAR(8000)
-- asigantion a blanc de cette variable
SET @ALL_PRENOMS = ''
-- concatène la variable avec un nouveau prénom pour chaque ligne lue en évitant les NULL
SELECT @ALL_PRENOMS = @ALL_PRENOMS + COALESCE(JOR_PRENOM + ', ', '')
FROM   T_JOUEUR_JOR
-- supprime la virgule finale parasite si la chaîne est assignée
IF @ALL_PRENOMS IS NOT NULL
   SET @ALL_PRENOMS = SUBSTRING(@ALL_PRENOMS, 1, LEN(@ALL_PRENOMS) - 1)
-- restitue l'information
SELECT @ALL_PRENOMS AS PRENOMS

PRENOMS
----------------------------------------------------------- 
Marcel, Gérard, Victor, Eugène, Didier
(1 ligne(s) affectée(s))

Les éléments importants à prendre en considération sont :

  • la préassignation de la variable à une chaine vide, sinon, comme toute variable encore non assignée, elle est marquée à NULL et la concaténation de NULL avec n'importe quoi, donne toujours NULL.
  • l'utilisation du COALESCE afin d'éviter la rencontre d'une occurrence marquée à NULL dans la colonne JOR_PRENOM.

III. Remplacer un curseur par un jeu de requête avec table temporaire

Nous allons maintenant nous intéresser à réaliser une procédure permettant de recalculer toutes statistiques de toutes les tables de la base de données. Une telle procédure nécessite l'appel de la commande UPDATE STATISTICS qui ne peut se faire que pour une table donnée, d'où le recours à un curseur :

 
Sélectionnez
DECLARE C_STATS CURSOR 
FOR 
   SELECT TABLE_SCHEMA, TABLE_NAME
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  TABLE_TYPE = 'BASE TABLE'

OPEN C_STATS

DECLARE @TABLE VARCHAR(128), @SCHEMA VARCHAR(128)
DECLARE @SQL VARCHAR(8000)

FETCH C_STATS INTO @TABLE, @SCHEMA

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = 'UPDATE STATISTICS ' + @TABLE + '.' + @SCHEMA 
   EXEC (@SQL)
   FETCH C_STATS INTO @TABLE, @SCHEMA
END
CLOSE C_STATS
DEALLOCATE C_STATS

Pour remplacer un tel code par une procédure sans curseur il faut utiliser une astuce qui consiste à créer une table temporaire dans laquelle nous allons puiser un élément à travailler à l'aide de la clause spécifique à SQL serve : TOP 1. Lorsque cet élément aura été travaillé en lançant une requête dynamique, nous marquerons que le travail a été effectué en donnant une valeur particulière à une colonne spécialement établie à cet effet lors de la création de la table temporaire.
À partir de cet instant, cette ligne va disparaitre du jeu appelé par le SELECT TOP 1. Petit à petit tout sera traité !

 
Sélectionnez
-- création et remplissage de la table temporaire de travail
-- notez l'ajout de la colonne OK préassignée à la valeur zéro
SELECT TABLE_NAME, 0 AS OK
INTO   #UPDSTAT
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'
DECLARE @SQL VARCHAR(8000)
DECLARE @TBL VARCHAR(128)
-- tant qu'il y a au moins une table à traiter
WHILE EXISTS(SELECT * 
             FROM   #UPDSTAT 
             WHERE  OK = 0)
BEGIN
-- prendre le nom de la première table à traiter
   SET @TBL = (SELECT TOP 1 TABLE_NAME 
               FROM   #UPDSTAT WHERE OK = 0
               ORDER  BY 1)
-- construire le texte du traitement 
   SET @SQL = 'UPDATE STATISTICS [' + @TBL + ']'
-- exécuter le traitement           
   EXEC (@SQL)
-- flaguer l'objet traité dans la table des objets à traiter
   UPDATE #UPDSTAT
   SET    OK = 1
   WHERE  TABLE_NAME = @TBL
END

-- supprimez la table temporaire de traitement
DROP TABLE #UPDSTAT

IV. Procédures non documentées

MS SQL Server, est doté depuis la version 6.5 de deux procédures non documentées permettant de réaliser un traitement pour chaque base de données et pour chaque table dans une base donnée. Ces procédures ont pour nom : sp_MSforeachdb et sp_MSforeachtable (attention, respectez bien la casse exacte avant du nom des procédures).

Ces deux procédures acceptent en paramètre une chaîne de caractères qui sera exécutée pour chaque base ou pour chaque table. En bonus, la présence du signe « ? » (point d'interrogation) dans la chaîne de caractères sera remplacée par le nom de la base ou de la table. Attention cependant, dans le cas du nom de la table, ce dernier est toujours composé des deux parties nom_propriétaire.nom_table. Si vous ne désirez obtenir que la partie nom de table, il faudra utiliser la fonction SUBSTRING combiné a la fonction CHARINDEX, de cette manière :

 
Sélectionnez
SUBSTRING(''?'', CHARINDEX(''.'', ''?'') + 2, LEN(''?'') - CHARINDEX(''.'', ''?'') - 2

Voici deux exemples d'utilisation de ces fonctions :

 
Sélectionnez
-- affichage du nombre de table de chaque base :
sp_MSforeachdb 'SELECT COUNT(*) AS "?" FROM ?.INFORMATION_SCHEMA.TABLES'

Northwind   
----------- 
31

master      
----------- 
37

model       
----------- 
2

msdb        
----------- 
82

pubs        
----------- 
14

tempdb      
----------- 
3


-- affichage du nombre de colonne de chaque table dans une base donnée :
sp_MSforeachtable 
  'SELECT COUNT(*) AS [?]  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 
        SUBSTRING(''?'', CHARINDEX(''.'', ''?'') + 2, LEN(''?'') - CHARINDEX(''.'', ''?'') - 2)'

[dbo].[Orders] 
-------------- 
14

[dbo].[Products] 
---------------- 
10

[dbo].[Order Details] 
--------------------- 
5

[dbo].[CustomerCustomerDemo] 
---------------------------- 
2

[dbo].[CustomerDemographics] 
---------------------------- 
2

[dbo].[Region] 
-------------- 
2

[dbo].[Territories] 
------------------- 
3

[dbo].[EmployeeTerritories] 
--------------------------- 
2

[dbo].[Employees] 
----------------- 
18

[dbo].[Categories] 
------------------ 
4

[dbo].[Customers] 
----------------- 
11

[dbo].[Shippers] 
---------------- 
3

[dbo].[Suppliers] 
----------------- 
12

(La procédure a été exécutée dans la base exemple Northwind.)

Attention cependant, qui dit procédure non documentée, dit portabilité suspecte, donc précautions. N'utilisez ces procédures, que dans un cadre léger et souple, par exemple une maintenance susceptible d'évoluer, et testez la non régression à chaque patch comme à chaque nouvelle version de MS SQL Server.

Remerciements à Webman pour la relecture orthographique de cet article, ainsi qu'aux membres de la rubrique SGBD.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2005 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.