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 "paramètre reniflant"

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

Mise à jour 1

L'article qui suit vous présentera le "paramètre de détection" 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 composants internes du serveur SQL (qui peuvent piquer).

Si vous le comprenez, vous saurez que le compromis avec la recompilation d'option peut entraîner une diminution des performances si l'instruction est exécutée très souvent.

Personnellement, j’ajoute l’option de recompilation une fois que je sais que la cause fondamentale est le reniflement des paramètres et que je la laisse à moins d’un problème de performances. La réécriture d'une instruction pour éviter une mauvaise analyse des paramètres entraîne une perte d'intention et réduit la maintenabilité. Mais il existe des cas où la réécriture est justifiée (utilisez de bons commentaires lorsque vous le faites).

Mise à jour 2

La meilleure lecture que j’ai eu à ce sujet a été publiée au chapitre 32 intitulé "Paramètres de détection: votre meilleur ami ... sauf quand ce n’est pas par" de GRANT FRITCHEY

C'est recommandé

Deep Dives SQL Server MVP, 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