Why are parameters slower than literal values in a where clause?
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.
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.
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.
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
下一篇: 为什么参数比where子句中的文字值慢？