Desempenho AddWithValue e implicações de cache do plano

Eu recomendo sempre usar o método que permite definir explicitamente o tipo de dados e a precisão. Sim, isso é mais trabalhoso, tanto no início quanto se o tipo de dados no banco de dados mudar (o que é comum quando, digamos, uma IDENTITYcoluna excede o intervalo de inte é aumentada para bigint).

cmd.parameters.Add("@VehicleId", SqlDbType.Int).Value = Vehicles.VehicleId;

Isso é simplesmente uma prática melhor, já que outras formas AddWithValue()podem causar problemas. Por exemplo, os tipos errados serão inferidos aqui:

cmd.parameters.AddWithValue("@intParam", 5);        -- tinyint
cmd.parameters.AddWithValue("@intParam", 257);      -- smallint
cmd.parameters.AddWithValue("@varchar20", "bob");   -- nvarchar(3)
cmd.parameters.AddWithValue("@varchar20", "frank"); -- nvarchar(5)

No intintervalo, é improvável que você encontre muitos problemas, mas se você estender isso para outros tipos e estiver usando consultas ad hoc em oposição a chamadas de procedimento armazenado com parâmetros fortemente tipados, as coisas podem ficar muito ruins muito rapidamente . Como David apontou, você pode obter planos ruins, por exemplo, conversões implícitas que podem fazer com que as buscas mudem para varreduras. Permitir que o padrão nvarcharseja passado para uma varcharcoluna pode causar grandes problemas de desempenho, como Jonathan Kehayias demonstra aqui. E permitir que o .NET determine o comprimento do parâmetro com base no comprimento da string pode - dependendo da versão do SQL Server e das configurações de parametrização - levar a um plano diferente para cada comprimento único de string passado. consultas específicas mais lentas, mas podem desperdiçar muita memória, pois haverá um plano redundante armazenado e usado para cada comprimento.

Essa pergunta já foi feita em outros lugares:

• Fóruns do MSDN
• Estouro de pilha

E Joel Coehoorn também blogou sobre os problemas de conversão de tipos, recomendando que você fique longe AddWithValue()(mesmo nos casos em que você "sabe" que o problema não existe):

• Já podemos parar de usar AddWithValue()?

Essa conversão afeta o desempenho do SQL Server no caso de um 'INT'?

Não. Alguns tipos de dados .NET mapeiam exatamente para os tipos de dados do SQL Server, e int é um deles. E este método não está obsoleto. Veja os documentos aqui

Os problemas causados ​​por não especificar exatamente os tipos de parâmetros são geralmente limitados a planos ruins. Se você passar um parâmetro com uma precedência de tipo de dados mais alta do que a coluna para a qual ele mapeia, os valores da coluna devem ser convertidos no tipo do parâmetro para comparação. E isso impedirá o uso do índice e exigirá uma conversão por linha.

Outro problema com a derivação do tipo de parâmetro string do valor é que o SqlClient do .NET definirá o comprimento do parâmetro para o comprimento real da string passada. Isso pode minimizar o tráfego de rede e os requisitos de memória, mas pode alterar o cache do plano de consulta e o comportamento de reutilização no SQL Server. Para invocações de procedimento armazenado, não importa, mas para lotes SQL ad-hoc, você pode acabar com um plano em cache diferente para cada comprimento de string.

Se você passar um parâmetro numérico/decimal com precisão excessiva, isso pode causar a perda de escala no resultado, de acordo com as regras aqui , mas os parâmetros normalmente não aparecem nos cálculos.

O tipo será o tipo .NET. Se houver um mapa direto, não há problema. Se não houver um mapa direto, acho que tentará uma conversão implícita.

string command = "select * from table where CustomerID = @ID and name = @name and ddate = @ddate ";
SqlCommand cmd = new SqlCommand(command, new SqlConnection());
int id = 1;
string name  = "name";
DateTime dt = new DateTime(1900,1,1);
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@ddate", dt);
foreach (SqlParameter p in cmd.Parameters)
{
    Debug.WriteLine($"{p.ParameterName} {p.DbType}");
}