Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

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…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié mardi 16 mars 2010 16:51 par christian

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01