共用方式為

LES CURSEURS SOUS SQL SERVER

  • 發行項

EVITER LES CURSEURS, OUI MAIS…

« N'utilisez pas les curseurs, ils ne sont pas performants » est une phrase qui revient souvent dans la bouche des DBA.

Certes, une grande partie des traitements de boucle sont remplaçables par des traitements ensemblistes, d'autant

plus que le moteur relationnel est optimisé pour ce type de tâche.

Notons au passage que parmi les « templates » de code T-SQL fournis avec SQL Server Mangement Studio, l'outil

de développement et d'administration fourni avec SQL Server 2005, les curseurs ne figurent plus dans la liste.

Cependant, il est parfois nécessaire d'exécuter un traitement pour chaque ligne d'un résultat, comme définir une

concaténation de caractères, lancer un traitement spécifique…

Comment éviter donc l'usage du curseur ?

METHODES ALTERNATIVES A L'UTILISATION DES CURSEURS

Il existe de nombreuses techniques alternatives à l'utilisation des curseurs dont chacune s'adapte généralement plus

dans un contexte précis.

Explorons trois de ces techniques.

La première consiste à utiliser une requête avec paramètre, la deuxième à utiliser une variable de type table avec une

boucle While et la troisième à faire appel à définir une procédure stockée en .NET.

Requête avec paramètre

Un besoin qui fréquemment est de pouvoir concaténer le résultat des champs d'une requête.

Le principe est d'empiler les résultats d'un SELECT dans une variable.

Prenons un exemple sur la table HumanResources.Department de la base exemple de SQL Server 2005, AdventureWorks.

Concaténons le nom des départements.

La fonction COALESCE permet de traiter le cas des valeurs nulles.

Variable table avec une boucle While

Le principe est d'utiliser une variable de type table, de l'alimenter et boucler en fonction du nombre de lignes insérées,

information obtenue grâce à la variable globale @@ROWCOUNT.

A la différence de la première technique, celle-ci permet d'effectuer un traitement pour chaque ligne recherchée.

Sur ce principe recompilons chaque procédure de notre base à l'aide du code suivant :

La liste des procédures de la base est obtenue en interrogeant la vue système de SQL Server 2005 sys.objects. Pour

les versions ultérieures, il faudra utiliser la vue sysobjects. Une colonne de type IDENTITY a été définie sur notre variable

table afin de pouvoir effectuer le traitement définie sur chaque élément de notre résultat.

Procédure en C#

L'apport de la CLR dans SQL Server donne une très grande richesse pour certains traitements la manipulation de

caractères ou des calculs mathématiques qui sont moins efficaces en T-SQL et surtout plus fastidieux à écrire.

Regardons à travers l'exemple ci-dessous le fonctionnement d'une procédure en C#.

La manière de coder une procédure en .NET est très proche de celle employée dans ADO.NET.

Les différences notables sont surtout au niveau de l'objet « connection » et de l'objet « SqlPipe » permet de définir le

« resultset » à renvoyer au client.

L'instruction ("context connection=true")) précise que la connexion utilisée est celle de SQL Server. Dans cet exemple, l'ob-

USE AdventureWorks

DECLARE @DptName varchar(max);

SET @DptName = '';

select @DptName= @DptName + COALESCE(Name +',','') from HumanResources.Department;

IF @DptName IS NOT NULL

SET @DptName = SUBSTRING(@DptName,1,LEN(@DptName)-1)

SELECT @DptName

SET NOCOUNT ON

DECLARE @rows int, @j int;

DECLARE @schemaName nvarchar(128);

DECLARE @name nvarchar(128);

DECLARE @procName nvarchar(128);

DECLARE @procTable TABLE(pk int IDENTITY (1, 1),schema_name nvarchar(128) , name nvarchar(128));

INSERT INTO @procTable

SELECT schema_name(schema_id),name FROM sys.objects WHERE type in ('P','U','V') and name not

in('sp_helpdiagrams','sp_creatediagram','sp_alterdiagram', 'sp_dropdiagram', 'sp_renamediagram',

'sp_upgraddiagrams','sp_helpdiagramdefinition')

SET @rows = @@ROWCOUNT;

SET @j = 1;

WHILE @j<= @rows

BEGIN

SELECT @schemaName = schema_name, @name = name FROM @procTable WHERE pk = @j;

SET @procName = @schemaName +'.'+@name

EXECUTE sp_recompile @procName

SET @j = @j + 1;

END;

jet SqlPipe renvoie une chaîne de caractères mais nous pouvons aussi renvoyer un enregistrement ou une table.

Notons tout de même à propos de cette nouvelle fonctionnalité que les manipulations d'ensembles seront toujours

plus performantes à l'aide des instructions T-SQL.

A PROPOS DES PERFORMANCES

Connaissez-vous la règle des 80-20 ? Elle consiste à optimiser 20 pourcent des traitements les plus couteux pour

améliorer 80 pourcent des performances du serveur.

Chaque base de données possède ses spécificités. Ainsi, même deux bases ayant la même structure et des volumétries

identiques peuvent avoir des performances différentes.

En conclusion, ne vous jetez pas sur la réécriture de tous vos curseurs et passez, au contraire, davantage de temps

sur vos traitements couteux en ressources, d'autant que, parfois, le curseur sera plus performant que d'autres techniques…

SQL Server nous fournit plusieurs d'outils de monitoring tels que le profiler, le perfmon, les instructions SET

STATISTICS IO, SET STATISTICS TIME…

using System.Data;

using System.Data.SqlClient;

using System.Transactions;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure()]

public static void SampleSP(int rating)

{

using (SqlConnection conn = new SqlConnection("context connection=true"))

{

conn.Open();

string rsResult;

SqlCommand cmd = new SqlCommand(

"SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor " +

"WHERE CreditRating <= @rating", conn);

cmd.Parameters.AddWithValue("@rating", rating);

SqlDataReader r = cmd.ExecuteReader();

while (r.Read())

{

// effectue des traitements dans la boucle

}

SqlContext.Pipe.Send(rsResult);

}

}

}

REFERENCES

T-SQL Coding standards :

https://msdn.microsoft.com/library/default.asp?url=/library/

en-us/dnsqlpro04/html/sp04l9.asp

Best SQL Server Performance Tuning Tips:

https://www.sql-serverperformance.

com/best_sql_server_performance_tips.asp

Cursor performance :

https://www.sqlteam.com/item.asp?ItemID=5761

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR

https://msdn.microsoft.com/library/default.asp?url=/library/

en-us/dnsql90/html/mandataaccess.asp

Comments