首页 > 代码库 > 【转】 参数化查询

【转】 参数化查询

SQL SERVER在执行查语句时会生成查询计划,并将查询计划缓存在数据库中,如果下次执行相同的SQL 语句时,会利用缓存的执行计划,而不必重新编译生成执行计划。

使用参数化查询,可以提高查询计划的重用率,提高执行效率。这里以 SQL SERVER 2005为例,分析一下SQL SERVER查询的参数化。

在 SQL SERVER 2005中,可以使用以下 SQL SERVER语句查看缓存的执行计划:

1 SELECT usecounts, cacheobjtype, objtype, text2 3 FROM sys . dm_exec_cached_plans4 5 CROSS APPLY sys . dm_exec_sql_text ( plan_handle)6 7 ORDER BY usecounts DESC ;

 

分析如下查询代码的执行:

1 command.CommandText = "select * from t1 where col1 = ‘abc‘";2 command.ExecuteNonQuery();3 command.CommandText = "select * from t1 where col1 = ‘bcd‘";4 command.ExecuteNonQuery(); 

首先用 DBCC FreeProcCache命令清空缓存,以免其它查询计划的缓存影响分析,然后执行代码,然后查看缓存的计划有两个:

select * from t1 where col2 = ‘abc‘

select * from t1 where col2 = ‘bcd‘

可以看到 SQL SERVER为两个查询分别生成了一个查询计划,使用次数为 1,并没有重用任何查询计划。

 

      但如果我们使用参数化查询方式

1 command.CommandText = "select * from t1 where col1 = @str";2 command.Parameters.Add("@str", "abc");3 command.ExecuteNonQuery();4 command.Parameters[0].Value = http://www.mamicode.com/"bcd";5 command.ExecuteNonQuery(); 

执行后生成的查询计划为

(@strnvarchar(3))Select * From t1 Where col1 = @str

 

SQL SERVER生成了一个参数化的查询计划,并且这个查询计划的使用次数为 2, SQL SERVER重用了这个查询计划。

      当然这样参数化查询并不是最好的,尤其是对字符串类型的参数化查询, SQL SERVER会为不同长度的参数生成不同的查询计划,如:

command.CommandText = "select * from t1 where col1 = @str";command.Parameters.Add("@str", "abc");command.ExecuteNonQuery();command.Parameters[0].Value = http://www.mamicode.com/"abcd";command.ExecuteNonQuery(); 

SQL SERVER生成两个查询计划,

(@str nvarchar(4))Select * From t1 Where col1 = @str

(@str nvarchar(3))Select * From t1 Where col1 = @str,

因此使用存储过程是重用查询计划的最好方案,因为 SQL SERVER会将整个存储过程生成的查询计划缓存,无论传入参数是什么,每次都会重用存储过程的查询计划。

【转】 参数化查询