SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    1 Commentaire

Depuis un certain temps je fais tout pour éviter d’utiliser les curseurs de façon explicite.
la solution que j’ai trouvée et qui marche presque à tous les coups est la suivante :
1) Création de table temporaire (#Tmptable)
2) Remplissage de la table temporaire
3) WHILE EXISTS ( SELECT … FROM #Tmptable)
BEGIN
SET @ligneAtraiter = SELECT TOP 1 … FROM #Tmptable
instrustion à exécuter avec @ligneAtraiter
DROP de la ligne traitée dans la table #Tmptable
END
4) DROP de la table #Tmptable à la fin
Et j’en étais fier de ne pas utiliser les curseurs. Et les arguments ne manquent pas pour justifier ce choix…Mais à ma grande déception le curseur explicite est plus performant que mes SELECT TOP1 … DELETE
Voici la démonstration :

Préparation : Mesure de la durée d’exécution et pages logiques lues
=========================================================================================
D’abord j’ai préparé une instruction pour mesurer le temps CPU et le nombre de pages logiques lues par mes requêtes.

-->Initialisation des mesures <br />
SET NOCOUNT ON <br />
DECLARE @cpu_timedebut INT <br />
DECLARE @logical_reads_debut INT <br />
BEGIN <br />
DBCC DROPCLEANBUFFERS <br />
DBCC FREEPROCCACHE <br />
DBCC FREESYSTEMCACHE ('ALL') <br />
SELECT @cpu_timedebut = cpu_time       -- Temps CPU (en millisecondes) utilisé par la demande <br />
, @logical_reads_debut = logical_reads -- Nombre de lectures logiques effectuées par la demande <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid  <br />
 <br />
-->====== Début de l'instrustion à mesurer  ====== <br />
..... <br />
..... <br />
..... <br />
-->====== Fin de l'instrustion à mesurer  ======  <br />
 <br />
-->Afficher les mesures <br />
SELECT cpu_time - @cpu_timedebut AS [Temps CPU (ms)]  <br />
, logical_reads - @logical_reads_debut AS [Nb Lectures Logiques] <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid ;

Préparation : Configuration de ma machine de test
=========================================================================================

Nom du système d’exploitation : Microsoft Windows XP Professionnel
Version du système : 5.1.2600 Service Pack 3 version 2600
Processeur(s): 1 processeur(s) installé(s).[01]: x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1828 MHz
Mémoire physique totale : 2 046 Mo

Mesure pour le curseur
=========================================================================================
Scénario : afficher toutes les bases de données de mon instance dont le nom commence par AdventureWorks. Dans mon cas j’ai la liste suivante qui s’affiche :
AdventureWorks
AdventureWorksDW
AdventureWorksLT
AdventureWorksLT2008

/******************************************************************************************** <br />
DESCRIPTION : Utilisation du CURSEUR pour afficher toutes les bases de données de mon instance dont le nom commence par AdventureWorks.  <br />
Auteur : Etienne ZINZINDOHOUE <br />
*********************************************************************************************/ <br />
--Initialisation des mesures <br />
SET NOCOUNT ON <br />
DECLARE @cpu_timedebut INT <br />
DECLARE @logical_reads_debut INT <br />
BEGIN <br />
DBCC DROPCLEANBUFFERS <br />
DBCC FREEPROCCACHE <br />
DBCC FREESYSTEMCACHE ('ALL') <br />
 <br />
SELECT @cpu_timedebut = cpu_time       -- Temps CPU (en millisecondes) utilisé par l'instrustion <br />
, @logical_reads_debut = logical_reads -- Nombre de lectures logiques effectuées par l'instrustion <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid  <br />
 <br />
--====== DEBUT DE L'INSTRUSTION  ====== <br />
DECLARE @BaseEnCoursTraitement VARCHAR(50); <br />
DECLARE baseATraiter_cursor CURSOR STATIC LOCAL FORWARD_ONLY FOR <br />
SELECT [name] AS [NomBase] <br />
FROM sys.databases <br />
WHERE [name] LIKE 'AdventureWorks%' <br />
ORDER BY [NomBase] <br />
 <br />
OPEN baseATraiter_cursor <br />
FETCH NEXT FROM baseATraiter_cursor INTO @BaseEnCoursTraitement <br />
WHILE @@FETCH_STATUS = 0 <br />
BEGIN <br />
PRINT @BaseEnCoursTraitement <br />
-- Au suivant <br />
FETCH NEXT FROM baseATraiter_cursor INTO @BaseEnCoursTraitement <br />
END <br />
CLOSE baseATraiter_cursor <br />
DEALLOCATE baseATraiter_cursor <br />
--====== FIN DE L'INSTRUSTION ====== <br />
--Afficher les mesures <br />
SELECT cpu_time - @cpu_timedebut AS [Temps CPU (ms)]  <br />
, logical_reads - @logical_reads_debut AS [Nb Lectures Logiques] <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid ; <br />
END  <br />

Mesure pour le SELECT TOP 1 … DELETE
=========================================================================================

 <br />
/***************************************************************************************** <br />
DESCRIPTION : Utilisation du SELECT TOP 1 ... DELETE pour afficher toutes les bases de données de mon instance dont le nom commence par AdventureWorks.  <br />
Auteur : Etienne ZINZINDOHOUE  <br />
*****************************************************************************************/ <br />
--Initialisation des mesures <br />
SET NOCOUNT ON <br />
DECLARE @cpu_timedebut INT <br />
DECLARE @logical_reads_debut INT <br />
BEGIN <br />
DBCC DROPCLEANBUFFERS <br />
DBCC FREEPROCCACHE <br />
DBCC FREESYSTEMCACHE ('ALL') <br />
 <br />
SELECT @cpu_timedebut = cpu_time       -- Temps CPU (en millisecondes) utilisé par la demande <br />
, @logical_reads_debut = logical_reads -- Nombre de lectures logiques effectuées par la demande <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid  <br />
 <br />
--====== DEBUT DE L'INSTRUSTION  ====== <br />
DECLARE @BaseEnCoursTraitement VARCHAR(50),@SQL VARCHAR (max), @DEL VARCHAR (max)  <br />
CREATE TABLE #LISTE_BASES (nom_base varchar (50))  <br />
INSERT INTO #LISTE_BASES   <br />
SELECT [name] AS [NomBase]  <br />
FROM sys.databases  <br />
WHERE [name] LIKE 'AdventureWorks%' <br />
ORDER BY [NomBase]  <br />
-- Tant qu'il y a des lignes dans la table temporaire  <br />
WHILE EXISTS ( SELECT nom_base  <br />
  FROM #LISTE_BASES  <br />
  )   <br />
BEGIN  <br />
--Se positionner sur la 1ère ligne de la table temporaire  <br />
SET @BaseEnCoursTraitement = (SELECT TOP 1 nom_base FROM #LISTE_BASES)  <br />
-- Appliquer le Niveau de compatibilité SQL 2005  <br />
PRINT @BaseEnCoursTraitement <br />
-- Supprimer la base traitée de la table temporaire  <br />
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE nom_base = '''+ @BaseEnCoursTraitement + ''';'  <br />
EXEC (@DEL)  <br />
END  <br />
--Supprimer la table temporaire  <br />
DROP TABLE #LISTE_BASES   <br />
--====== FIN DE L'INSTRUSTION ====== <br />
--Afficher les mesures <br />
SELECT cpu_time - @cpu_timedebut AS [Temps CPU (ms)]  <br />
, logical_reads - @logical_reads_debut AS [Nb Lectures Logiques] <br />
FROM sys.dm_exec_requests <br />
WHERE session_id = @@spid ; <br />
END

Résultats
=========================================================================================

–#### 1er Essai ###

–> Avec Le curseur
Temps CPU (ms) Nb Lectures Logiques
0 85

–> Avec Le SELECT TOP 1 …DELETE
Temps CPU (ms) Nb Lectures Logiques
47 477

–#### 2ème Essai ###

–> Avec Le curseur
Temps CPU (ms) Nb Lectures Logiques
0 85

–> Avec Le SELECT TOP 1 …DELETE
Temps CPU (ms) Nb Lectures Logiques
78 471

–#### 3ème Essai ###

–> Avec Le curseur
Temps CPU (ms) Nb Lectures Logiques
0 85

–> Avec Le SELECT TOP 1 …DELETE
Temps CPU (ms) Nb Lectures Logiques
47 472

Pour les 3 essais le constat est que : le CURSEUR est plus performant que mes SELECT TOP 1 … DELETE