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

1
2
3
4
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 :

1
2
3
4
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) :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 :

1
2
3
4
5
6
7
8
9
10
11
12
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.

Share This