AddWithValue without DBType causing queries to run slowly

Question

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.

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.

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.

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.

[EDIT]

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

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

[UPDATE]

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.

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.

Answer 1

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.

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

Answer 2

I just ran into this EXACT problem. I've got a legacy database with a lot of char columns. Without specifying the type of column the results took a couple of minutes on one of my queries. (It default to nvarchar.) Specifying the column type caused the results to take seconds.

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

I think I'm going to try having every string query as a char type and see how that goes.

[edit]

Actually... after reading this: http://www.u2u.info/Blogs/U2U/Lists/Posts/Post.aspx?ID=11

I've decided to go with this solution:

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