问题描述
我在使用MS SQL Server 2008时遇到问题:
当我使用硬编码字符串作为参数执行查询时,我的查询运行得很快,但当我使用字符串参数时,查询花费的时间更长!
常量字符串查询花费1秒,而另一个花费11秒。
代码如下:
常量字符串(1秒):
SELECT * FROM VIEWCONTENTS WHERE COUNTRY = 'ZA' AND CONTENTTYPE = 'A' AND TASK = 'R23562';
参数化(11秒):
DECLARE @country AS CHAR(2); SET @country = 'ZA'; SELECT * FROM VIEWCONTENTS WHERE COUNTRY = @country AND CONTENTTYPE = 'A' AND TASK = 'R23562'
推荐答案
在查询末尾使用选项(重新编译)。所以:
DECLARE @country AS CHAR(2); SET @country = 'ZA'; SELECT * FROM VIEWCONTENTS WHERE COUNTRY = @country AND CONTENTTYPE = 'A' AND TASK = 'R23562' OPTION (RECOMPILE)