I. Paramétrage des requêtes côté client (3)

pas indiqués, des valeurs de paramètre de longueurs différentes peuvent potentiellement produire des plans paramétrés différents. Ceci peut causer une dégradation significative de performance pour SQL Server 2005 puisque toutes ces requêtes paramétrées seront stockées dans le même compartiment de hachage.

La raison est qu’elles ont le même texte et pour SQL Server 2005 seul le texte est utilisé pour le hachage, pas les paramètres. Pour les applications où il est impossible de changer le code côté de client, employer le Trace Flag 144 pour forcer le mappage des types côté serveur avec une longueur constante. Quand l’application a des requêtes qui sont incorrectement paramétrées, ce Trace Flag peut être très utile. Ceci aura comme conséquence seulement un plan paramétré :

(@1 tinyint,@2 varchar(8000))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2

Noter que ce Trace Flag s’applique de façon globale au serveur. C’est dont une option qui pourrait avoir aussi de fâcheuse conséquence et doit être utilisée avec prudence. La méthode recommandée pour paramétrer les requêtes côté client est illustrée ci-dessous :

command.CommandText = "Select * From t1 Where col1 = @id And col2 = @str";
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters.Add("@str", SqlDbType.VarChar, 50);
command.Parameters[0].Value = 1;
command.Parameters[1].Value = "abc";
command.ExecuteNonQuery();
command.Parameters[0].Value = 2;
command.Parameters[1].Value = "abcd";
command.ExecuteNonQuery();

Conséquence, seulement un plan compilé pour les deux exécutions : Quelques points clés à noter. Nous avons paramétré la requête en indiquant le type de données et la longueur maximum pour les paramètres (nous indiquons la longueur maximum du paramètre Varchar en fonction de la colonne de la table). L’optimiseur essaiera de détecter les valeurs de paramètre (Parameter Sniffing) afin de choisir un plan optimal. Tandis que dans la plupart des cas ceci fonctionne à notre avantage, dans certains cas où les premières valeurs passées sont atypiques, il peut s’orienter sur un mauvais plan. Nous pouvons faire mieux que l’exemple ci-dessus en reportant la préparation de requête au moment de l’exécution :

command.CommandText = "Select * From t1 Where col1 = @id And col2 = @str";
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters.Add("@str", SqlDbType.VarChar, 50);
command.Parameters[0].Value = 1;
command.Parameters[1].Value = "abc";
command.Prepare(); command.ExecuteNonQuery();
command.Parameters[0].Value = 2;
command.Parameters[1].Value = "abcd";
command.ExecuteNonQuery();

Ici la préparation de requête est reportée au moment d’exécution et nous gagnons tous les avantages du paramétrage y compris le fait que nous ne devons pas envoyer le texte de la requête SQL sur le réseau à chaque fois. Pour ODBC l’attribut SQL_SOPT_SS_ DEFER_PREPARE (pour OLEBD c’est SSPROP_DEFERPREPARE) détermine si l’instruction est préparée immédiatement ou reportée à l’exécution (Voir la documentation en ligne pour plus d’information).

Au niveau du serveur, nous noterons un appel RPC a sp_prepexec avec le texte complet puis uniquement des appels RPC a sp_execute avec juste le handle et les paramètres.