[SQL Server] Curseurs : comparaison de performances avec une boucle WHILE

Ce petit billet offre quelques éléments de comparaison de performances entre un curseur et une boucle WHILE. Il est de notoriété publique que les curseurs sont à éviter et qu’il est de bon ton de les remplacer par, par exemple, une boucle WHILE pour des raisons de performances.

Nous allons nous intéresser aux statistiques I/O et aux temps d’exécution. De plus, chaque type de curseur sera testé (FAST_FORWARD, STATIC, KEYSET et DYNAMIC).

Pour une présentation générale sur les curseurs, vous pouvez aller ici.

Avant de commencer…

Créons une table MaTable (au sein d’une base de tests MaBase préalablement créée) :

USE MaBase
GO
CREATE TABLE MaTable(MaTable_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, MaTable_Nom NVARCHAR(50))
GO

Insérons 10 000 enregistrements :

USE MaBase
GO
INSERT INTO MaTable (MaTable_Nom) VALUES('Test')
GO 10000

Entrée dans le vif du sujet…

Création et lancement de notre curseur MonCurseur (par défaut, il est global) :

SET NOCOUNT ON
DECLARE MonCurseur CURSOR
FOR
   SELECT MaTable_ID, MaTable_Nom
   FROM MaTable DECLARE @id INT DECLARE @nom NVARCHAR(50)
OPEN MonCurseur
FETCH NEXT FROM MonCurseur INTO @id, @nom
WHILE @@FETCH_STATUS=0
   BEGIN
     PRINT (CAST(@id AS VARCHAR(5))+'.)'+@nom)
     FETCH NEXT FROM MonCurseur
   END
CLOSE MonCurseur
DEALLOCATE MonCurseur
GO

Remplacez <Type> par l’un des 4 types de curseurs : FAST_FORWARD, STATIC, KEYSET ou DYNAMIC.

Création et lancement de la boucle WHILE :

SET NOCOUNT ON
DECLARE @n INT,@id INT, @nom NVARCHAR(50)
SET @n=1
SET @id=0
WHILE @n&gt;0
  BEGIN
     SELECT TOP 1 @id=MaTable_ID, @nom=MaTable_Nom FROM MaTable WHERE MaTable_ID&gt;=@id
     SET @n=@@ROWCOUNT
     PRINT (CAST(@id AS VARCHAR(5))+'.)'+@nom)
     SET @id+= 1
  END
GO

Ces différents scripts permettent de parcourir chaque ligne de la table MaTable et d’y afficher son contenu.

Tableau des résultats

Les différents tests ont été effectués sur SQL Server 2012 tournant sur une machine locale dotée d’une RAM de 8 Go, de processeurs Intel i7 quadri-cœur et cadencé à 2,3 Ghz. Et la base de données tempDB est placée sur un disque SSD qui favorise les performances en écritures.

Voici le tableau des résultats de nos différents tests (chaque script a été exécuté plusieurs fois pour arriver à un temps de réponse moyen cohérent) :

Boucle WHILE Curseur FAST_FORWARD Curseur STATIC Curseur KEYSET Curseur DYNAMIC
Nombre de lectures logiques 20002 10032 40291 60243 41324
Temps d’exécution 2 secs. 33 secs. 54 secs. 1 min 2 secs. 1 min 56 secs.

Les lectures logiques ont été récupérées via le SQL Profiler (qui permet notamment d’effectuer une somme de toutes les lectures logiques réalisées, contrairement à un simple SET STATISTICS IO ON).

Comme on peut le noter :

  • Le choix du type de curseur joue un rôle non-négligeable sur les performances. Ainsi, les curseurs sont généralement peu rapides, sauf si l’on utilise le type FAST_FORWARD qui, au passage, effectue moins de lectures logiques.
  • La boucle WHILE offre des temps de réponse plus intéressants que les différents types de curseur, mais est plus consommatrice en lectures logiques qu’un curseur de type FAST_FORWARD. Cela permet de déduire qu’un curseur FAST_FORWARD peut s’avérer utile si l’on souhaite traiter un ensemble de résultats en une seule fois sans trop consommer de ressources.

Pour aller plus loin…

Les tests ont permis de mettre en application l’idée selon laquelle les curseurs sont généralement lents. Toutefois, les tests effectués ne constituent pas forcément une vérité absolue, d’autant plus qu’il est de bon ton de savoir que différents facteurs peuvent influer sur les résultats, tels que la qualité du matériel (RAM, CPU,…), la façon dont SQL Server a été paramétré, l’état du cache, la quantité de données à traiter, l’indexation,…

N’hésitez pas à effectuer une pile de tests suivant divers contextes (insertion de données, portée globale/locale,…).

Dans un prochain billet, nous étudierons différentes alternatives ensemblistes à l’utilisation des curseurs.

Publicités

2 commentaires sur “[SQL Server] Curseurs : comparaison de performances avec une boucle WHILE

  1. Jibidus dit :

    Bonjour,
    Il y a quelques fautes de frappes dans le script dédié au while (@id1 et @name1 ne sont pas déclarées).
    Une petite erreur s’est aussi glissée dans le script du curseur : le mot clé INTO n’est pas repris dans le second FETCH. Et le n’est pas présent non plus.
    Sinon, en faisant le même test sur l’une de mes bases de données, j’obtiens des résultats relativement différents (sur SQL Server 2008) :
    Pour 1 000 000 lignes, 26 secondes dans les deux cas (cursor FAST_FORWARD et while).
    Comme vous le disiez, cela doit beaucoup dépendre de l’infrastructure et du paramétrage de la base.

    • Bonjour Jibidus,

      Merci pour ton commentaire. Je viens de corriger.

      Sinon, effectivement, les FAST FORWARD ne sont pas toujours plus lents qu’une boucle WHILE. Rien de mieux que des tests pour se décider suivant différents contextes, mais une chose est sûre: SQL Server a été surtout optimisé pour le traitement de requêtes ensemblistes.

      @+!

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