2

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?

8

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

4

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.

  • 1
    However can't this still be an issue if, say, the value fits into tinyint or smallint? IIRC the issue was much more noticeable with varchar/nvarchar - both when a column is varchar and .NET is implicitly creating an nvarchar, or when .NET would implicitly create an nvarchar(5) for Smith, an nvarchar(6) for Browne, and an nvarchar(8) for Bertrand. – Aaron Bertrand Jan 22 '18 at 17:51
  • Yes a parameter of type int compared to a column of type smallint would require a conversion on the column values. And yes nvarchar parameters compared to varchar columns is the most common source of trouble. – David Browne - Microsoft Jan 22 '18 at 17:53
0

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}");
}
  • There are actually two steps involved: one is mapping the .NET value to a database type (for example, the string you passed gets mapped to NVARCHAR(4)), and the second one is that data type being implicitly converted by the engine to the column type when the query is executed (to like, say, VARCHAR(50)). Ideally, you want to avoid the first round of inferring the type by not using .AddWithValue at all, and instead using .Add with the database type and length explicit, thereby avoiding the conversion in the engine. – Jeroen Mostert Jan 22 '18 at 23:47
  • @JeroenMostert Uh, I think I like said that with "If there is not a direct map I think it will try an implicit conversion." – paparazzo Jan 22 '18 at 23:52
  • If you did, that's not what I got out of it. Specifically, no conversion is happening in the crucial first step of mapping the .NET type to a database type. The value is unchanged. There is always a "direct map" to a DB type unless you're talking UDTs, so that part wouldn't make sense when applying it to the type inference. – Jeroen Mostert Jan 22 '18 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.