sp_executesql est lent avec les paramètres

dapper sql-server

Question

J'utilise dapper-dot-net en tant qu'ORM et il produit le code SQL suivant, lent (1700ms).

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (@id1,@id2) AND SensorId = @sensor AND SensorValue != -32768 AND SensorValue != -32767',N'@id1 bigint,@id2 bigint,@sensor int',@id1=139,@id2=726,@sensor=178

Lorsque je modifie ce code en supprimant les paramètres, la requête s'exécute rapidement (20 ms). L'absence de ces paramètres devrait-elle réellement faire la différence et pourquoi?

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'

Réponse acceptée

Ajouter OPTION (RECOMPILE) à la fin

... AND SensorValue != -32767 OPTION (RECOMPILE) 

Je soupçonne que vous rencontrez "reniflage de paramètres"

Si tel est le cas, nous pouvons le laisser avec l'option OPTION ou envisager des alternatives

Mise à jour 1

L'article suivant vous présentera "reniflage de paramètres" http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

Je vous conseille de connaître les tenants et les aboutissants car cela vous aidera à mieux comprendre les internes des serveurs SQL (qui peuvent mordre).

Si vous le comprenez, vous saurez que le compromis avec l'option recompiler peut être une diminution de performance si l'instruction est exécutée très souvent.

Personnellement, j'ajoute une option de recompilation après que je sache que la cause principale est le reniflage de paramètres et le laisse à moins qu'il y ait un problème de performance. Réécrire une instruction pour éviter que le reniflage de mauvais paramètres entraîne une perte d'intention, ce qui réduit la maintenabilité. Mais il y a des cas où la réécriture est justifiée (utilisez de bons commentaires quand vous le faites).

Mise à jour 2

La meilleure lecture que j'ai eue sur le sujet était dans le chapitre 32 intitulé "Parameter sniffing: your best friend ... sauf quand ce n'est pas par" par GRANT FRITCHEY

C'est recommandé.

SQL Server MVP Deep Dives, Volume 2


Réponse populaire

J'ai récemment rencontré le même problème. La première chose que j'ai faite a été d'ajouter un index de couverture non clusterisé sur les colonnes de mon instruction where.

Cela a amélioré le temps d'exécution sur SQL, mais lorsque Dapper exécutait la requête, il était encore lent, en fait, il était dépassé.

Ensuite, je me suis rendu compte que la requête générée par dapper transmettait le paramètre nvarchar (4000) où ma colonne de table de base de données était un varchar (80). indexe si cela n'a pas de sens pour vous.) en réalisant cela, j'ai mis à jour mon dapper où la déclaration est la suivante:

WHERE Reference = convert (varchar (80), @ Reference)

L'exécution de l'instruction with the where ci-dessus a entraîné une recherche d'index et une amélioration de 100% des performances.

Juste pour ajouter: l'option (recompiler) n'a pas fonctionné pour moi.

Et après tout ce morceau et cette danse, il y a un moyen de dire à Dapper de le faire par défaut:

Dapper.SqlMapper.AddTypeMap (typeof (chaîne), System.Data.DbType.AnsiString);

Par défaut, tous les paramètres de chaîne seront mappés sur un varchar (4000) plutôt que sur un nvarchar (4000). Si vous avez besoin de la comparaison de chaînes Unicode, vous pouvez explicitement effectuer la conversion sur le paramètre.




Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi