MS SQL Server
  1. MS SQL Server
  3. Empêcher ADO.NET d'utiliser sp_executesql
Intereting Posts
Comment faire pour renvoyer correctement le message d'erreur de la console à l'agent SQL Server Version SQL Server 2008 de OVER (… Rows Unbounded Preceding) SQL Server: transformer des lignes en colonnes Comment contrôler le nombre de lignes renvoyées dans une requête SQL, sans connaître le nombre de lignes dont nous avons besoin SSMS – Interrogation de plusieurs servers dans une même requête sum trois colonnes dans trois tables différentes en utilisant la requête union? Comment vérifier si le champ est NULL et vide? Comment exécuter deux procédure stockée Oracle à partir d'une procédure stockée SQL Server 2008 R2 à l'aide du server lié Comment empêcher une application multi-thread de lire ce même logging Sql Server deux fois Interrogation croisée possible avec différents droits d'access? Comment récupérer les valeurs de return de procédure stockée à partir d'un TableAdapter Quel est le moyen le plus rapide de convertir toutes les colonnes et relations GUID PK et FK en int? Oracle équivalent de SQL Server SCHEMA Dois-je créer les objects de support requirejs pour utiliser la création de diagrammes de database? Requêtes SQL avec des résultats différents

Empêcher ADO.NET d'utiliser sp_executesql

Dans notre database SQL Server 2005 (testée à l'aide de Management Studio avec DBCC FREEPROCCACHE et DBCC DROPCLEANBUFFERS ), l'instruction suivante est rapide (~ 0.2s de compilation, ~ 0.1s d'exécution): 

 SELECT ... FROM ... WHERE a = 1 AND b = '' ...

L'instruction suivante est cependant lente (~ 0,2 s de compilation, 7-11 s d' exécution): 

 exec sp_executesql N'SELECT ... FROM ... WHERE a = @a AND b = @b ...', N'@a int, @b nvarchar(4000), ...', @a=1, @b=N'', ...

SQL Server choisit un plan d'exécution différent, bien que les requêtes soient égales. Cela est logique puisque, dans le premier cas, SQL Server a les valeurs réelles de a , b et tous les autres parameters disponibles et peut utiliser les statistics pour créer un meilleur plan. Apparemment, le plan de requête pour les valeurs concrètes des parameters est bien meilleur que celui générique et l'emporte définitivement sur tout avantage de performance de "caching de plan de requête".

Maintenant, ma question: ADO.NET semble toujours utiliser la deuxième option (sp_executesql) lors de l'exécution de requêtes paramétrées, ce qui est généralement logique (caching de plan de requête, etc.). Dans notre cas, cependant, cela tue la performance. Donc, y a-t-il un moyen de

  • forcer ADO.NET à utiliser quelque chose de différent de sp_executesql (c'est-à-dire, quelque chose où l'parsingur de requêtes SQL Server prend en count les valeurs de parameters réelles) OU
  • forcer SQL Server à reclassr le plan de requête du SQL transmis à sp_executesql tenant count des valeurs de paramètre ?

Et s'il vous plaît ne me dites pas que je dois revenir à laide, vieux, dangereux sql = "WHERE b = " + quoteAndEscape(parameterB) …

Mettre le SQL dans une procédure stockée ne fait aucune différence (lent, avec et sans WITH RECOMPILE ). Je n'ai pas publié l'instruction SQL réelle car elle est assez complexe (se joint à plusieurs tables, y compris les sous-SELECT et l'agrégation).

Vieux fil je sais, mais je viens de le find en googlant à peu près exactement la même phrase! J'avais exactement le même problème (la requête fonctionnait très rapidement dans Management Studio à l'aide de parameters, mais ensuite très lentement via ADO.Net) et répliquait le problème en exécutant la requête dans Management Studio via "exec sp_execute". Les deux plans d'exécution étaient très différents, même avec l'indicateur Optimisation pour les requêtes. Au lieu de cela, j'ai effectué une sélection initiale de certaines données dans une table temporaire. Cela a semblé faire la différence, et étant donné que vous dites que votre requête est complexe, elle pourrait très bien faire la différence dans votre cas aussi – je ne suis pas sûr de la façon dont cela fonctionnait, mais il semblait que le plan d'exécution ligne même en utilisant sp_execute.

Vous pouvez essayer l' indicateur de requête OPTIMIZE FOR qui (quote):

Indique à l'optimiseur de requête d'utiliser une valeur particulière pour une variable locale lorsque la requête est compilée et optimisée. La valeur est utilisée uniquement lors de l'optimization de la requête, et non lors de l'exécution de la requête. OPTIMIZE FOR peut contrer le comportement de détection des parameters de l'optimiseur ou peut être utilisé lorsque vous créez des guides de plan

Je crois que le problème est lié à l'utilisation du type de données VARCHAR dans la database. SQL Server ne semble pas utiliser l'index spécifié si le paramètre where est déclaré en tant que NVARCHAR .

Vous pouvez, cependant, modifier votre colonne de database en NVARCHAR (cela augmenterait la taille, bien sûr) et les performances de l'index s'amélioreront probablement.

Je rencontre actuellement ce problème avec LINQ, et peut avoir besoin de revenir à l'utilisation de procédures stockées pour contourner ce problème.

Le problème est expliqué en détail dans cette discussion Microsoft Connect

Je voudrais déplacer la requête à la procédure stockée, puis dans la command spécifiez command.CommandType = CommandType.StoredProcedure.

Cela ne crée pas sp_executesql et augmente les performances