ADO.NET: Safe pour spécifier -1 pour SqlParameter.Size pour tous les parameters VarChar?

Nous avons un corps existant de code C # qui appelle des requêtes SQL Server ad-hoc paramétrées dans de nombreux endroits. Nous ne spécifions jamais SqlParameter.Size, et il est documenté que dans ce cas, la class SqlParameter déduit la taille de la valeur du paramètre. Nous avons récemment pris connaissance des problèmes de pollution du cache de plan SQL Server que cela crée, où un plan distinct est mis en cache pour chaque combinaison distincte de tailles de paramètre.

Heureusement, chaque fois que nous créons un SqlParameter, nous le faisons via une seule méthode utilitaire, nous avons donc la possibilité d'append quelques lignes à cette méthode et de faire disparaître ce problème. Nous envisageons d'append ce qui suit:

if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar)) m_sqlParam.Size = -1; 

En d'autres termes, chaque fois que nous passons un paramètre varchar, transmettez-le en tant que varchar (max). Basé sur quelques tests rapides, cela fonctionne très bien, et nous pouvons voir (via SQL Profiler et sys.dm_exec_cached_plans) qu'il y a maintenant un seul plan dans le cache pour chaque requête ad-hoc, et le type de paramètre (s) de la string est maintenant varchar (max).

Cela semble être une solution si facile qu'il doit y avoir un inconvénient caché et destructeur de performance. Est-ce que quelqu'un est au courant d'un?

• 'Nom d'object invalide' pour la table temporaire lors de l'utilisation de la command avec des parameters
• Comment puis-je récupérer une list de parameters d'une procédure stockée dans SQL Server
• Comment utiliser SqlCommand pour CREATE DATABASE avec un nom de database paramétré?
• Insertion de valeurs dans une database SQL Server à l'aide d'ado.net via C #
• Comment fonctionne la récupération des données de SQL Server vers SqlDataReader?

(Notez que nous ne devons prendre en charge que SQL Server 2008 et versions ultérieures.)

Mise à jour (16 janvier)

Oui, il y a un inconvénient caché, destructeur de performance!

Un grand merci à Martin Smith, dont la réponse (voir ci-dessous) m'a indiqué la bonne façon d'parsingr cela. J'ai testé avec la table des users de notre application, qui a une colonne Email définie comme nvarchar (100), et a un index non-cluster (IX_Users_Email) sur la colonne Email. J'ai modifié l'exemple de requête de Martin comme suit:

 declare @a nvarchar(max) = cast('a' as nvarchar(max)) --declare @a nvarchar(100) = cast('a' as nvarchar(100)) --declare @a nvarchar(4000) = cast('a' as nvarchar(4000)) select Email from Users where Email = @a 

En fonction de laquelle des déclarations "declare" je ne commente pas, j'obtiens un plan de requête TRÈS différent. Les versions nvarchar (100) et nvarchar (4000) me donnent toutes deux une search d' index sur IX_Users_Email – en fait, toute longueur que je spécifie me donne le même plan. La version nvarchar (max), en revanche, me donne un scan d' index sur IX_Users_Email, suivi d'un opérateur Filter pour appliquer le prédicat.

C'est assez pour moi – s'il y a une possibilité d'get un scan plutôt qu'une search, alors cette "guérison" est pire que la maladie.

Nouvelle proposition

J'ai remarqué que chaque fois que SQL Server paramètre une requête avec un paramètre varchar, le plan mis en cache utilise simplement varchar (8000) (ou nvarchar (4000)) pour le paramètre. Je me dis si c'est assez bon pour SQL Server, c'est assez bon pour moi! Remplacer le code C # dans ma question originale (ci-dessus) avec:

 if(sqlDbType == SqlDbType.VarChar) m_sqlParam.Size = 8000; else if(sqlDbType == SqlDbType.NVarChar) m_sqlParam.Size = 4000; 

Cela semble résoudre le problème de pollution de cache de plan sans le même impact sur les plans de requête que l'utilisation d'une taille de -1. Cependant, je n'ai pas fait beaucoup de tests avec cela, et je suis très intéressé d'entendre les commentaires de quiconque sur cette approche révisée.

Mise à jour (24 septembre)

Nous avons dû modifier la version précédente (Nouvelle proposition, ci-dessus) pour gérer le cas où la valeur du paramètre est plus longue que le maximum. À ce stade, vous n'avez pas d'autre choix que de faire un varchar (max):

 if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar)) { m_sqlParam.Size = (sqlDbType == SqlDbType.VarChar) ? 8000 : 4000; if((value != null) && !(value is DBNull) && (value.ToSsortingng().Length > m_sqlParam.Size)) m_sqlParam.Size = -1; } 

Nous utilisons cette version depuis environ six mois sans problème.

• Appel asynchronous d'une procédure stockée SQL Server en C #
• Utilisation de la transaction NHibernate dans SqlBulkCopy
• C # SQLConnection pooling
• Erreur lors de l'insertion de données à l'aide de SqlBulkCopy
• Impossible de se connecter à localDB dans VS2012 – "Une erreur liée au réseau ou à une instance spécifique s'est produite lors de l'établissement d'une connection à SQL Server …"

 CREATE TABLE #T ( X VARCHAR(10) PRIMARY KEY ) DECLARE @A VARCHAR(MAX) = CAST('A' AS VARCHAR(MAX)) SELECT * FROM #T WHERE X = @A