AddWithValue performance and plan cache implications

Question

Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:

cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);

In the database, the data type for VehicleID is INT.

As I understand it, because AddWithValue (which is deprecated) does not specify the data type/length, this '@VehicleID' is converted and potentially converted incorrectly. Does that conversion affect SQL Server performance in the case of an 'INT'?

Would this then cause issues beyond plan cache pollution? Is there a performance hit caused by the conversion?

Answer 1

I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (which is common when, say, an IDENTITY column exceeds the range of int and it is upsized to bigint).

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

This is simply better practice, since other forms of AddWithValue() can cause issues. For example, the wrong types will be inferred here:

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)

In the int range you're unlikely to come across much in the way of problems, but if you extend this to other types, and are using ad hoc queries as opposed to stored procedure calls with strongly typed parameters, things can get very bad very quickly. As David pointed out, you can get bad plans, for example implicit conversions that can make seeks switch to scans. Allowing the default nvarchar to be passed through to a varchar column can cause massive performance issues, as Jonathan Kehayias demonstrates here. And allowing .NET to determine the length of the parameter based on the length of the string can - depending on version of SQL Server and parameterization settings - lead to a different plan for every unique length of string passed in. This won't make those specific queries slower, but they can waste a lot of memory, since there will be a redundant plan stored and used for each length.

This question has been asked in other places:

• MSDN Forums
• Stack Overflow

And Joel Coehoorn has blogged about the type conversion issues too, recommending you stay away from AddWithValue() (even in cases where you "know" the problem doesn't exist):

• Can we stop using AddWithValue() already?

Answer 2

Does that conversion affect SQL Server performance in the case of an 'INT'?

No. Some .NET data types map exactly to SQL Server data types, and int is one of them. And this method is not deprecated. See the docs here

The issues caused by not specifying parameter types exactly are usually limited to bad plans. If you pass a parameter with a higher data type precedence than the column it maps to, then the column values must be converted to the parameter's type for comparison. And this will prevent index use, and require a conversion per row.

Another issue with deriving the string parameter type from the value is that .NET's SqlClient will set the length of the parameter to the actual length of the string passed. This may minimize the network traffic and memory requirements, but can change the query plan caching and reuse behavior in SQL Server. For stored procedure invocations it won't matter, but for ad-hoc SQL batches, you can end up with a different cached plan for each string length.

If you pass a numeric/decimal parameter with excessive precision this could potentially cause the loss of scale in the result, per the rules here, but parameters don't typically appear in calculations.

Answer 3

The type will be the .NET type. If there is a direct map then no problem. If there is not a direct map I think it will try an implicit conversion.

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}");
}