SQL Server查询使用参数比使用常量字符串需要更长的时间

I'm facing a problem with MS SQL Server 2008 which is:

我遇到的MS SQL Server 2008问题是:

When I execute a query using a hard-coded string as a parameter, my query run fast but when I use a string parameter instead, the query takes longer!
Constant string query takes 1 second while the other takes 11 seconds.

当我使用硬编码字符串作为参数执行查询时,我的查询运行速度很快,但是当我使用字符串参数时,查询需要更长时间!常量字符串查询需要1秒,而另一个需要11秒。

Here are the codes bellow:

以下是代码:

Constant string (1 second):

常量字符串(1秒):

     SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = 'ZA' 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562';

Parameterized (11 seconds):

参数化(11秒):

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'

2 个解决方案

#1

Use OPTION (RECOMPILE) at the end of your query. So:

在查询结尾处使用OPTION(RECOMPILE)。所以:

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'
OPTION (RECOMPILE)

#2

What does this yield?

这会产生什么?

DECLARE @country AS VARCHAR(2); 
SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'

How about this?

这个怎么样?

DECLARE @country AS CHAR(2); 
DECLARE @country1 AS VARCHAR(2); 
SET @country = 'ZA';
SET @country1 = @country; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'

#1