没有 DBType 的 AddWithValue 导致查询运行缓慢

Question

我一直在使用 cmd.Parameters.AddWithValue，并且没有指定 DBType（int、varchar、...）来运行查询。 查看 SQL Profiler 后，似乎使用此方法运行的查询比指定数据类型时运行得慢很多。

为了让您了解它慢了多少，这里有一个例子。 该查询是对单个表的简单查找，where 语句中的列已建立索引。 当指定数据类型时，某个查询的运行时间约为 0 MS（对于 sql server 来说太小了，无法测量），并且需要 41 次读取。 当我删除 DBType 时，可能需要大约 200 毫秒和 10000 次读取才能完成查询。

我不确定这是否只是 SQL Profiler 错误报告了值，或者这些值实际上是否正确，但它是可重现的，因为我可以添加和删除 DBType，并且它将生成 SQL Profiler 中给出的值。

有没有其他人遇到过这个问题，以及解决它的简单方法。 我意识到我可以在我的所有代码中添加数据类型，但这似乎需要添加很多东西，如果有更简单的方法来修复它，那将非常感激。

[编辑]

经过一些初始测试（循环运行两个场景）后，分析器给出的值似乎是准确的。

正如补充信息一样，我在 Windows XP Pro 上运行 .Net 2.0，在 Windows 2000 上运行 SQL Server 2000 作为数据库。

[更新]

经过一番挖掘，我找到了这个 博客文章，可能相关。 似乎 .Net 中的字符串值（因为它们是 unicode）会自动创建为 nvarchar 参数。 我必须等到周一开始工作，看看是否可以做一些事情来解决这个问题。 看起来我仍然必须设置数据类型，这是我试图避免的。

这个问题并没有出现在我所做的每个查询中，只是选择了几个查询，所以我仍然可能只是在有问题的查询中设置 DBType，但我正在寻找一个更通用的问题解决方案。

Answer 1

该问题与 SQL Server 如何进行隐式类型转换有关。 如果使用 NVARCHAR 值（即 N'some text'）过滤 VARCHAR 列，SQL 别无选择，只能将该列转换为 NVARCHAR，因为 NVARCHAR 无法隐式转换回 VARCHAR。

最好的解决方法是指定类型或将数据库列更改为 NVARCHAR。

Answer 2

我刚刚遇到了这个确切的问题。 我有一个包含很多字符列的旧数据库。 在没有指定列类型的情况下，我的一个查询花了几分钟才得到结果。 （默认为 nvarchar。）指定列类型导致结果需要几秒钟的时间。

cmd.Parameters.AddWithValue("charcolumn", "stringvalue");
cmd.Parameters[0].SqlDbType = SqlDbType.Char;

我想我将尝试将每个字符串查询作为字符类型，看看效果如何。

更新

实际上，在阅读这篇博客文章后:^存档

为什么 ADO.Net 这么慢？

由于 .Net 中的字符串是 unicode，因此这些类型会映射到 NVARCHAR，并且如果基础列恰好是 VARCHAR 类型，则 SQL Server 会在每次查询时再次使用比较运算符之前将整个列转换为 NVARCHAR！ 如果我们知道输入字符串可以轻松地写为 VARCHAR，这会导致大量不必要的 IO。 换句话说：从 NVARCHAR 变量声明中删除 N 可以将查询性能提高至少 20 倍！

解决方案！

cmd.CommandText = "从 dbo.adonetdemo 中选择 *，其中跟踪字符串 = @tstring"; 
  cmd.Parameters.AddWithValue("tstring","4E0A-4F89-AE"); 
  cmd.Parameters[0].DbType = System.Data.DbType.AnsiString; 
  

我决定采用此解决方案：

cmd.Parameters.AddWithValue(colName, val);
if(val is string)
  cmd.Parameters[i].DbType = DbType.AnsiString;

Answer 3

这两种情况生成的 SQL 语句是什么？

我怀疑当您没有明确指定它时，它会将值假定为 varchar 。

例如 从订单中选择 OrderId WHERE OrderId = 1001
VS 从订单中选择 OrderId WHERE OrderId = '1001'