[SQL Server] Curseurs : solutions alternatives

Ce billet présente quelques alternatives  à l’utilisation des curseurs. Les exemples traités seront volontairement simplistes.

Avant de commencer…

Voici la définition de quelques tables qui seront utilisées dans nos exemples :

  • Services :
CREATE TABLE Services
(
Service_ID INT IDENTITY (1,1) PRIMARY KEY,
Service_Nom VARCHAR (128),
Service_Effectif INT)
  • Employes  :
CREATE TABLE Employes
(
 Emp_ID INT IDENTITY (1,1) PRIMARY KEY,
 Emp_Nom VARCHAR(20),
 Emp_Prenom VARCHAR(30),
 Age INT,
 Service_ID INT FOREIGN KEY REFERENCES Services (Service_ID)
)

Insertion de données :

  • Services :
INSERT INTO Services (Service_Nom) VALUES ('A')
INSERT INTO Services (Service_Nom) VALUES ('B')
INSERT INTO Services (Service_Nom) VALUES ('C')
INSERT INTO Services (Service_Nom) VALUES ('D')
GO
  • Employes  :
INSERT INTO Employes (Emp_Nom,Emp_Prenom,Age,Salaire,Service_ID) VALUES ('Fitte','Pierre',55,5000,FLOOR(RAND()*4)+1)
INSERT INTO Employes (Emp_Nom,Emp_Prenom,Age,Salaire,Service_ID) VALUES ('Ette','Jacques',32,2999,FLOOR(RAND()*4)+1)
INSERT INTO Employes (Emp_Nom,Emp_Prenom,Age,Salaire,Service_ID) VALUES ('Naref','Paul',34,3000,FLOOR(RAND()*4)+1)
INSERT INTO Employes (Emp_Nom,Emp_Prenom,Age,Salaire,Service_ID) VALUES ('Neymar','Jean',28,2779,FLOOR(RAND()*4)+1)
INSERT INTO Employes (Emp_Nom,Emp_Prenom,Age,Salaire,Service_ID) VALUES ('Croc','Odile',41,4543,FLOOR(RAND()*4)+1)
GO

La fonction RAND() est, ici, utilisée pour affecter n’importe quel ID (compris entre 1 et 4, soit le nombre d’enregistrements au sein de la table Services) à Service_ID.

Remplacement d’un curseur par…

… une requête utilisant une table temporaire

Exemple : curseur qui effectue le calcul du nombre total d’employés par service à partir de la table Employe, suivi d’une mise-à-jour de la colonne Effectif de la table Service pour tenir compte des résultats :

DECLARE @ID INT, @NbEmployes INT
DECLARE CurseurServices CURSOR
FOR
 SELECT Service_ID
FROM Services OPEN CurseurServices FETCH CurseurServices INTO @ID
WHILE @@FETCH_STATUS= 0
BEGIN
   SELECT @NbEmployes=COUNT(1)
   FROM Employes
   WHERE Service_ID=@ID

   UPDATE Services
   SET Service_Effectif=@NbEmployes
   WHERE Service_ID=@ID
   FETCH NEXT FROM CurseurServices INTO @ID
END
CLOSE CurseurServices
DEALLOCATE CurseurServices
GO
SELECT * FROM Services
GO

Remplacement en utilisant une table temporaire :

CREATE TABLE #TabTempServices (Service_ID INT PRIMARY KEY CLUSTERED, Nb_Employes INT)
INSERT INTO #TabTempServices (Service_ID, Nb_Employes)
SELECT Service_ID, COUNT(1) AS Nb_Employes
FROM Employes
GROUP BY Service_ID
ORDER BY Service_ID

UPDATE Services
SET Service_Effectif=(SELECT t.Nb_Employes
FROM #TabTempServices t
WHERE Services.Service_ID=t.Service_ID)
GO

… une requête utilisant principalement une boucle WHILE

Exemple : curseur qui effectue l’affichage ligne par ligne d’enregistrements préfixés d’un caractère particulier. Dans notre cas, le curseur procède à l’affichage textuel     des noms de tous les employés précédés de leur identifiant :

SET NOCOUNT ON DECLARE CurseurEmp
CURSOR
FOR
   SELECT Emp_ID, Emp_Nom
   FROM Employes
DECLARE @Emp_ID INT
DECLARE @Emp_Nom NVARCHAR(20)
OPEN CurseurEmp
   FETCH NEXT FROM CurseurEmp
   INTO @Emp_ID, @Emp_Nom
<span style="font-size: 12px; line-height: 18px;">WHILE @@FETCH_STATUS = 0</span>
BEGIN
   PRINT (CAST(@Emp_ID AS VARCHAR(5))+': '+@Emp_Nom)
   FETCH NEXT FROM CurseurEmp
   INTO @Emp_ID, @Emp_Nom
END
CLOSE CurseurEmp
DEALLOCATE CurseurEmp
GO

Remplacement par une requête avec une boucle WHILE :

SET NOCOUNT ON
DECLARE @n INT,@Emp_ID INT,@Emp_Nom VARCHAR(20)
SET @n = 1
SET @Emp_ID = 0
WHILE @n> 0
BEGIN
   SELECT TOP 1 @Emp_ID=Emp_ID, @Emp_Nom=Emp_Nom
   FROM Employes
   WHERE Emp_ID >= @Emp_ID

   SET @n=@@ROWCOUNT PRINT (CAST(@Emp_ID AS VARCHAR(5))+':' +@Emp_Nom)
   SET@Emp_ID+= 1
END
GO

… une requête simple ensembliste

Exemple : curseur qui effectue la suppression itérative d’un service (table Services) s’il constate qu’aucun employé n’en fait partie (table Employes) :

DECLARE @Service_ID
INT DECLARE CurseurEmpServ
CURSOR
FOR
   SELECT s.Service_ID
   FROM Services s
     LEFT JOIN Employes e ON s.Service_ID=e.Service_ID
   WHERE e.Service_ID IS NULL
OPEN CurseurEmpServ
   FETCH NEXT FROM CurseurEmpServ INTO @Service_ID
WHILE @@FETCH_STATUS = 0
BEGIN
   DELETE Services
   WHERE Service_ID=@Service_ID

   FETCH NEXT FROM CurseurEmpServ INTO @Service_ID
END
CLOSE CurseurEmpServ
DEALLOCATE CurseurEmpServ
GO

Remplacement par une requête simple :

DELETE s
FROM Services s
   LEFT JOIN Employes e
   ON s.Service_ID=e.Service_ID
WHERE e.Service_ID IS NULL
GO

… une requête utilisant CASE

Exemple : curseur qui parcourt chaque ligne d’une table et effectue une mise-à-jour si une condition particulière est rencontrée. Dans notre cas, augmentation de 10 du salaire de chaque employé gagnant plus de 1000, et de 50 pour ceux qui gagnent moins de 1000 :

DECLARE @ID INT, @Salaire MONEY
DECLARE CurseurEmp CURSOR
FOR
   SELECT Emp_ID, Salaire
   FROM Employes

OPEN CurseurEmp
   FETCH NEXT FROM CurseurEmp
   INTO @ID, @Salaire
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Salaire<1000
    UPDATE Employes
    SET Salaire = Salaire+50
    WHERE Emp_ID = @ID
  ELSE
    UPDATE Employes
    SET Salaire = Salaire+10
    WHERE Emp_ID = @ID
  FETCH NEXT FROM CurseurEmp
  INTO @ID, @Salaire
END
CLOSE CurseurEmp
DEALLOCATE CurseurEmp
GO

Remplacement en utilisant CASE :

UPDATE e
SET Salaire = CASE
WHEN e.Salaire < 1000 THEN Salaire+50
WHEN e.Salaire > 1000 THEN Salaire+10
END
FROM Employes e
GO

… une requête paramétrée utilisant COALESCE

Exemple : curseur qui effectue l’affichage de la liste de tous les employés avec, pour chaque ligne, l’identifiant, le nom, le prénom et le salaire traités pour être séparés par un espace (ainsi que par une barre verticale entre chaque employé) :

DECLARE @Emp_ID INT, @Emp_Nom VARCHAR(20), @Emp_Prenom VARCHAR(30), @Salaire MONEY
DECLARE @Emp_Infos VARCHAR(MAX)
SET @Emp_Infos = ''
DECLARE CurseurEmp CURSOR
FOR
   SELECT Emp_ID, Emp_Nom, Emp_Prenom, Salaire
   FROM Employes
OPEN CurseurEmp
   FETCH CurseurEmp INTO @Emp_ID, @Emp_Nom, @Emp_Prenom, @Salaire
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Emp_Infos = @Emp_Infos+'ID: '+CONVERT(VARCHAR(5),@Emp_ID)+', Nom: '+@Emp_Nom+', Prénom: '+@Emp_Prenom +', Salaire: '+CONVERT(VARCHAR(10),@Salaire)+' | '
   FETCH CurseurEmp
      INTO @Emp_ID, @Emp_Nom, @Emp_Prenom, @Salaire
END
CLOSE CurseurEmp
DEALLOCATE CurseurEmp
SELECT @Emp_Infos
GO

Remplacement par une simple requête paramétrée, avec COALESCE :

DECLARE @Emp_Infos VARCHAR(MAX)
SET @Emp_Infos = ''
SELECT @Emp_Infos = COALESCE(@Emp_Infos +'ID: ', '')+CAST(Emp_ID AS VARCHAR)+', Nom: '+Emp_Nom+', Prénom: '+Emp_Prenom+', Salaire: '+CAST(Salaire AS VARCHAR)+' | '
FROM Employes
SELECT @Emp_Infos AS Emp_Infos
GO

La fonction COALESCE permet de retourner des expressions non-nulles, l’idée étant de se prémunir contre toute valeur nulle au sein de la table traitée. Nous aborderons ses subtilités dans un billet indépendant, dans le futur.

En conclusion…

Comme on a pu le noter, remplacer des curseurs par des requêtes ensemblistes est possible. Nous avons notamment pu voir que cela pouvait se faire avec :

  • Des boucles WHILE.
  • Des tables temporaires.
  • Des instructions CASE.
  • Des requêtes paramétrées.
  • Des requêtes simples ensemblistes.

Il existe diverses autres façons de remplacer des curseurs, à savoir :

  • Utilisation des CTEs (Common Table Expressions), que nous aborderons dans un billet indépendant.
  • Utilisation de tables dérivées, que nous aborderons également dans une billet indépendant.
  • Utilisation de tables variables (notamment si vous avez peu d’enregistrements à traiter).
  • Exécution de requêtes multiples.
  • De packages SSIS/ETL.
  • Utilisation de procédures stockées non-documentées de Microsoft (i.e., sp_MSForEachDB qui permet de traiter un ensemble de bases de données d’une instance).
  • Etc…

Notons toutefois que les performances d’un curseur peuvent varier suivant les options utilisées. En règle générale, un curseur de type FAST_FORWARD travaille plus vite que n’importe quel autre curseur, et peut fournir des temps de réponse intéressants. Plus de détails ici et .

Dans tous les cas, SQL Server étant optimisé pour les opérations ensemblistes, privilégiez donc l’utilisation de requêtes ensemblistes.


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