Éviter les curseurs sous Microsoft SQL Server

Date de publication : 20/05/2005

Par SQLPro (autres articles) (CV)

niveau : intermédiaire

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... A travers les nombreux audits de bases de données que j'ai fait, 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...

1. Remplacer un curseur par une requête
2. Remplacer un curseur par une requete avec paramètre
3. Remplacer un curseur par un jeu de requête avec table temporaire
4. Procédures non documentées

1. 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 :

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) :

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 :

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 corélées. Une requête, même très complexe sera la plupart du temps plus efficace qu'un traitement iteratif à l'aide d'une boucle par curseur. Cela est dû à l'optimiseur.

2. Remplacer un curseur par une requete avec paramètre

Avec les données suivantes...

La table T_JOUEUR est composé 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 :

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 rapeller 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 :

-- 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 caîne 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.
'utilisation du COALESCE afin d'éviter la rencontre d'une occurrence marquée à NULL dans la colonne JOR_PRENOM.

3. 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'ou le recours à un curseur :

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 server : 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écialisement établie a cet effet lors de la création de la table temporaire.
A partir de cet instant, cette ligne va disparaitre du jeu appelé par le SELECT TOP 1. Petit à petit tout sera traité !

-- création et remplisage 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 tritement           
   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

4. 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 ci acant 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é 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 u tiliser la fonction SUBSTRING combiné a la fonction CHARINDEX, de cette manière :

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

Voici deux exemples d'utilisation de ces fonctions :

-- 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 procedure 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.

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. Cette page est déposée.

Responsable bénévole de la rubrique SGBD & SQL : Youghourta Benali (Djug) -

Developpez.com

Services

Partenaires