我一直在使用cmd.Parameters.AddWithValue，而不是指定DBTYPE（INT，VARCHAR，...）运行查询。细算SQL事件探查器，似乎用这种方法运行查询运行时，您指定的数据类型比慢很多。

I've been using cmd.Parameters.AddWithValue, and not specifying a DBType (int, varchar,...) to run queries. After looking at SQL Profiler, it seems that queries run with this method run a lot slower than when you specify the data type.

要给你慢多少是，这里有一个例子的想法。查询是一个简单的查找上单个表，并在其中陈述的列索引。当指定的数据类型，某个查询运行在约0 MS（太小的SQL Server来衡量），并且需要41读取。当我删除了DBTYPE，它可能需要大约200毫秒，和10000读取的查询来完成。

To give you an idea of how much slower it is, here's an example. The query is a simple lookup on a single table, and the column in the where statement is indexed. When specifying the data type, a certain query runs in about 0 MS (too small for sql server to measure), and requires 41 reads. When I remove the DBType, it can take around 200 ms, and 10000 reads for the query to complete.

我不知道，如果它只是SQL事件探查器误报值，如果这些值实际上是正确的，但它是可重复的，因为我可以添加和删除的DBTYPE，它会产生SQL事件探查器给出的值。

I'm not sure if it's just SQL Profiler misreporting values, or if these values are actually correct, but it is reproducible, in that I can add and remove the DBType, and it will produce the values given in SQL Profiler.

有其他人遇到这个问题，并修复它的简单方法。我知道我能去添加的所有数据类型在我的code，但好像很多东西添加进来，如果有一个更简单的方法来解决它，那将是多AP preciated。

Has anybody else come across this problem, and a simple way to fix it. I realize that I could go in adding the data type in all over my code, but that seems like a lot of stuff to add in, and if there is an easier way to fix it, that would be much appreciated.

在一些初始测试（运行这两种方案在循环中）看起来像值探查给出准确。

After some initial testing (running both scenarios in a loop) it seems like the values that profiler gives are accurate.

正如为DB添加的信息，我的Windows XP专业版运行.NET 2.0和SQL Server 2000在Windows 2000上。

Just as added information I'm running .Net 2.0 on Windows XP Pro, and SQL Server 2000 on Windows 2000 for the DB.

[更新]

经过一番周围挖，我能找到这个的博客文章，这可能与此有关。看来，字符串值在.net中（因为它们是单向code）将自动为nvarchar的参数创建。我将不得不等到周一，当我进入工作，看看我能做到围绕这里面解决问题的东西。不过这好像我会设定的数据类型，这是我一直在试图避免的。

After some digging around, I was able to find this blog post, which may be related. Seems that string values in .Net (since they are unicode) are automatically created as nvarchar parameters. I'll have to wait until monday when I get into work to see if I can do something around this which fixes the problem. Still it seems as though I would have to set the data type, which was what I was trying to avoid.

这个问题不会每次查询我所做的，只是有选择的几个露面，所以我仍然可能只是诉诸设置DbType与问题的疑问，但我正在寻找一个更普遍的解决问题的方法

This problem doesn't show up with every query I did, just a select few, so I still may just resort to setting the DBType in the queries with problems, but I'm looking for a more generalized solution to the problem.

推荐答案

该问题与SQL服务器如何做隐式类型转换。如果你使用一个nvarchar值（即N'some文本）过滤VARCHAR列，SQL没有选择，只能向列转换为NVARCHAR为NVARCHAR不能隐式转换回为VARCHAR。

The problem is related to how SQL server does implicit type conversions. If you filter a VARCHAR column using an NVARCHAR value (ie. N'some text'), SQL has no choice but to convert the column to NVARCHAR as NVARCHAR cannot be implicitly converted back to VARCHAR.

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

Your best workaround is either to specify the type or to change your database column to be NVARCHAR.