Sql-server – AddWithValue performance and plan cache implications

ado.netcparametersql-server-2016

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?

Best Answer

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:

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):