Discussion :

[orafrance]

Bonjour,

J'ai dû faire face à un problème de performance très pénible aujourd'hui. Nous avons une appli .Net qui s'est mise à partir complètement en sucette à cause d'une requête.

Mon analyse c'est que les plans d'exécution ont changé et que la-dite requête, très mal écrite, a mal supporté le changement

J'ai donc proposé une optimisation qui fonctionnait très bien... sauf dans l'appli. Finalement, on a mis le doigt sur le problème : cette requête est exécutée via sp_executesql avec des paramètres. Je pense que la chaîne de caractères envoyées ne change pas, du fait des paramètres, et le plan d'exécution n'est donc pas recalculé. En changeant juste le type des paramètres (nvarchar(32) -> nvarchar(10)) ça a suffit à forcer le calcul d'un nouveau plan et tout va bien maintenant.

Ma question est donc la suivante :
Considérant une requête du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exec N' SELECT name FROM matable WHERE id = @ids ',N'@ids nvarchar(14)',@ids=N'2ZYB'

Comment être sûr, qu'elle aura le même comportement que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT name FROM matable WHERE id = N'2ZYB'

A défaut, y a-t-il une meilleure manière de procéder alors que j'ai toujours vécu dans l'idée que les variables c'est bon, mangez-en et tout

Merci

[mikedavem]

Hello,

L'utilisation de sp_executesql permet de réutiliser le plan d'exécution comme tu l'as dit. Cependant il y a des avantages à cela mais aussi des inconvénients dans certains cas. En effet, l'optimiseur ne connaît pas à l'avance les valeurs des variables locales et va les "sniffer" pour produire le plan d'exécution d'où ton comportement. Si tu es dans ce cas et que cela te pose problème et que tu as la maîtrise du code tu peux toujours essayer d'utiliser OPTIMIZE FOR UNKNOWN. Dans ce cas l'optimiseur de requêtes va non plus utiliser les statistiques liées à la valeur utilisée lors de la première compilation mais va utiliser une hypothèse d'uniformité de valeurs dans la table en utilisation le vecteur de densité globale pour la colonne id X nombre de lignes totale dans la table. Ceci reste à tester évidemment car dans certains cas cela va régler ton problème mais pas forcément à tous les coups

++

[orafrance]

Bonjour,

Merci de ta réponse, j'avais déjà vu ce paramètre au détour de mes recherches mais je n'ai pas réussi à comprendre où cela était positionné ? Tout ce que j'ai lu laissait à penser que c'est une option que l'on met dans une procédure stockée appelé par sp_executesql. Or, nous n'exécutons pas de procédure mais des requêtes SQL.

Merci de ton aide

[mikedavem]

Tu peux utiliser cette option directement dans une requête SQL.

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT * 
FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );

++

[orafrance]

Merci infiniment. Sur l’entrefaite, j'ai trouvé une doc précisant tout ça : http://blogs.msdn.com/b/sqlprogramma...8-feature.aspx

[Kropernic]

Hello,

Je me permets d'intervenir malgré que la discussion soit taguée résolue car un point à attirer mon attention.

Dans le premier message il est dit :

J'ai donc proposé une optimisation qui fonctionnait très bien... sauf dans l'appli.

Cela peut être du au fait que, par défaut, SSMS et .NET (j'imagine que vous travailler en .NET) n'utilisent pas les mêmes valeurs par défaut pour certains paramètres. Or, vu que ces paramètres sont des caches_key, sql server va générer un plan de requête différent (du plan de requête utilisé dans SSMS lors de vos tests) pour la requête exécutée depuis l'application. Si ces paramètres n'ont pas été modifié, vous pourriez être dans cette problématique.

Plus d'info dans cet article (que je suis en train de traduire ^^).