Why are parameters slower than literal values in a where clause?

Question

Situation: c#, sql 2000

I have a table, lets call it 'mytable' with 30 million rows. The primary key is made up of fields A and B:

A char(16)
B smallint(2)

When i do a search like this, it runs really slowly (eg it does a full tablescan)

string a="a";
int b=1;
string sql = "select * from table(nolock) where a=@a and b=@b";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
  cmd.Parameters.AddWithValue("@a", a);
  cmd.Parameters.AddWithValue("@b", b);
  using (SqlDataReader rdr = cmd.ExecuteReader()) {...}
}

Change it to this however, and it runs really quick (eg it hits the index):

string where =
  String.Format("a='{0}' and b={1}", a, b);

string sql = "select * from table(nolock) where " + where;
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
  using (SqlDataReader rdr = cmd.ExecuteReader()) {...}
}

What on earth is going on? Seems strange to me.

Answer 1

Does it make any difference if you declare the b variable to be a short instead of int? Does it make any difference if you explicitly specify the types of the parameters? Does it make any difference if you use "where a=@a and b=@b" instead of the comma form?

I agree this does sound odd, and I wouldn't really expect any of these changes to help, but it's probably worth a try.

Answer 2

Do data types of parameter and column match? They don't it appears so datatype precedence applies

The column is smallint, but you send int. The column will be converted to int because it has a higher precedence. So it won't use an index.

Answer 3

You may tell SQL Server which index to use for a query. Use the WITH (INDEX = INDEX_ID) option where INDEX_ID is the ID of the index.

Get index ID's with:

SELECT i.indid, i.name FROM sysindexes i
INNER JOIN sysobjects o ON o.ID = i.id
WHERE o.Name = 'table'

So try then:

SELECT * FROM table(NOLOCK) WITH (INDEX = 1) WHERE a=@a and b=@b

Answer 4

As @gbn said, setting the data type should make it easy for you.

string where =
  String.Format("a='{0}' and b={1}", a, b);

In the example above, you are telling SQL to treat parameter a as char.
Whereas, in other example it will be treated as a varchar.

Use SQL profiler to see what is the SQL that gets executed in both the cases. That should clear it for you.

Answer 5

In the first case you are adding SqlParameter classes to the command. When the command is executed it is most likely generating DECLARE statements with the wrong data type. (You can verify this with a SQL trace.) If this is the case, the optimizer cannot select the correct index and falls back to a table scan.

If you use a stored proc instead, you would be forcing the parameters into the data types you declare. However, you can still do this from code if you specify the SqlDbType on the parameters.