为什么 where 子句中的参数比文字值慢? [英] Why are parameters slower than literal values in a where clause?

问题描述

情况:c#，sql 2000

Situation: c#, sql 2000

我有一张表，我们称它为mytable"，有 3000 万行.主键由字段 A 和 B 组成:

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

列是smallint，但你发送的是int.该列将被转换为 int，因为它具有更高的优先级.所以它不会使用索引.

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.

这篇关于为什么 where 子句中的参数比文字值慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！