主页 / dba / 问题 / 195937
Accepted
Peter
Asked: 2018-01-23 08:39:16 +0800 CST

AddWithValue 性能和计划缓存影响

  • 772

最近我注意到我公司的应用程序使用AddWithValue将参数值传递给动态的参数化查询。例子：

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

在数据库中，数据类型VehicleID为INT.

据我了解，由于 AddWithValue（已弃用）未指定数据类型/长度，因此此“@VehicleID”已转换并且可能转换不正确。在“INT”的情况下，这种转换会影响 SQL Server 的性能吗？

这会导致超出计划缓存污染的问题吗？转换是否会导致性能下降？

c# sql-server-2016

3 个回答

  1. Best Answer

    我强烈建议始终使用允许您显式设置数据类型和精度的方法。是的，这需要更多的工作，无论是前期还是如果数据库中的数据类型发生变化（例如，当IDENTITY列超出范围int并将其升迁为时，这很常见bigint）。

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

    这只是更好的做法，因为其他形式的AddWithValue()可能会导致问题。例如，这里会推断出错误的类型：

    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)

    在这个int范围内，您不太可能遇到很多问题，但是如果您将其扩展到其他类型，并且使用临时查询而不是具有强类型参数的存储过程调用，那么事情很快就会变得非常糟糕. 正如 David 指出的那样，您可能会遇到糟糕的计划，例如可以使搜索切换到扫描的隐式转换。允许将默认值nvarchar传递给varchar列可能会导致严重的性能问题，正如Jonathan Kehayias 在此处演示的那样. 并且允许 .NET 根据字符串的长度来确定参数的长度 - 根据 SQL Server 的版本和参数化设置 - 会为传入的每个唯一长度的字符串制定不同的计划。这不会使那些特定查询速度较慢，但​​它们会浪费大量内存，因为每个长度都会存储和使用一个冗余计划。

    这个问题在其他地方也有人问过：

    Joel Coehoorn也写过关于类型转换问题的博客，建议您远离AddWithValue()（即使在您“知道”问题不存在的情况下）：

    • 11

  2. 在“INT”的情况下，这种转换会影响 SQL Server 的性能吗？

    不，某些 .NET 数据类型完全映射到 SQL Server 数据类型，int 就是其中之一。而且这种方法没有被弃用。在此处查看文档

    未准确指定参数类型导致的问题通常仅限于糟糕的计划。如果传递的参数的数据类型优先级高于其映射到的列，则必须将列值转换为参数的类型以进行比较。这将阻止索引的使用，并要求每行进行一次转换。

    从值派生字符串参数类型的另一个问题是 .NET 的 SqlClient 会将参数的长度设置为传递的字符串的实际长度。这可以最大限度地减少网络流量和内存要求，但可以更改 SQL Server 中的查询计划缓存和重用行为。对于存储过程调用，这无关紧要，但对于临时 SQL 批处理，您最终可以为每个字符串长度获得不同的缓存计划。

    如果您传递精度过高的数字/十进制参数，则可能会导致结果中的比例损失，根据此处的规则，但参数通常不会出现在计算中。

    • 6

  3. 该类型将是 .NET 类型。如果有直接的地图，那么没有问题。如果没有直接映射，我认为它会尝试隐式转换。

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

相关问题