SQL Server vu par Christian Robert

SQL Server : Estimer le nombre de lignes renvoyées par une requête avec SQLCLR

Dans certains cas très particuliers il peut être intéressant de savoir le nombre de ligne que va renvoyer une requête sans exécuter cette dernière. En effet les opérations de comptage sont généralement gourmande en ressource est très difficile à optimiser pour un usage qui n'est au final pas toujours évident.

Or SQL Server est capable par le biais de son plan d'exécution de vous signaler le nombre de ligne que statistiquement il estime devoir renvoyer. Cela reste une estimation et en fonction du type de requête le résultat changera beaucoup par rapport au compte réel, mais le chiffre est généralement proche de la réalité.

Comment extraire cette valeur du plan et l'utiliser dans son code SQL ? C'est assez complexe car il faut normalement demander à générer le plan sous forme XML (SET SHOWPLAN_XML), or c'est strictement impossible à l'intérieure d'une procédure stockée sans passer par un SQL externe, comme une commande dynamique. Le plus simple est de passer par le support .Net intégré à SQL Server, le SQLCLR.

Il nous faut créer une procédure stockées, en effet les fonctions ne supporte pas l'appel de certaines commande SET comme celle que nous allons utiliser.

Voici le code :

[Microsoft.SqlServer.Server.SqlProcedure()]

public static SqlInt32 CountFirstResult(SqlString sqlQuery, out SqlDouble rowCount)

{

 

string sqlPlanOn = "SET SHOWPLAN_XML ON";

string sqlPlanOff = "SET SHOWPLAN_XML OFF";

 

SqlCommand sqlCmd;

 

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

{

 

sqlCnx.Open();

 

sqlCmd = new SqlCommand(sqlPlanOn, sqlCnx);

sqlCmd.ExecuteNonQuery();

 

// Set the showplan xml to ON

sqlCmd = new SqlCommand(sqlQuery.ToString(), sqlCnx);

XmlReader xmlQueryPlan = sqlCmd.ExecuteXmlReader();

 

if (xmlQueryPlan.ReadToFollowing("StmtSimple", @"http://schemas.microsoft.com/sqlserver/2004/07/showplan"))

rowCount = new SqlDouble(double.Parse(xmlQueryPlan.GetAttribute("StatementEstRows"),CultureInfo.InvariantCulture.NumberFormat));

else

rowCount = 0;

 

xmlQueryPlan.Close();

 

// Set the showplan xml to OFF

sqlCmd = new SqlCommand(sqlPlanOff, sqlCnx);

sqlCmd.ExecuteNonQuery();

 

sqlCnx.Close();

}

 

return 0;

}

Je me contente d'appeler les commandes SET SHOWPLAN_XML avant et après l'exécution de ma requête et j'extrais la valeur de l'attribut « StratementEstRows » qui est de type nombre à virgule flottante. Attention cela ne fonctionne que pour la première requête fournie dans l'état du code, mais il est possible de l'adapter pour obtenir plus d'info.

On teste le code comme suit :

DECLARE @ret float;
EXEC dbo.CountFirstResult 'SELECT * FROM sys.all_objects WHERE name like ''t%''', @ret OUT;
SELECT @ret;

Et on obtient un resultat de type numérique, dans mon cas : 841,614

Si vous ne voulez pas vous embarrasser avec la virgule, remplacez le float par un bigint comme suit :

DECLARE @ret bigint;
EXEC dbo.CountFirstResult 'SELECT * FROM sys.all_objects WHERE name like ''t%''', @ret OUT;
SELECT @ret;

Voilà pour le petit exemple de code SQLCLR pour faire une chose quasi impossible nativement dans SQL Server avec du T-SQL

En termes de resources, la requête n'est jamais utilisée, elle est uniquement compilé, ce qui ne fait que consommer un peu de resources processeur de l'ordre de quelques millisecondes.

Bon compte…