SQL Server query takes longer with parameter than with constant string

Question

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

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.

Here are the codes bellow:

Constant string (1 second):

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

Parameterized (11 seconds):

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

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

Have you compared the execution plans? Also read this: sqlperformance.com/2013/08/t-sql-queries/… — Aaron Bertrand
– Aaron Bertrand, Commented Aug 28, 2013 at 19:18
As the optimizer doesn't know the value of the local variable in the second query it is most likely changing the query plan to use a table/index scan rather than a seek. — steoleary
– steoleary, Commented Aug 28, 2013 at 20:30

ninjaPixel · Accepted Answer · 2013-10-23 13:20:29Z

3

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

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

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

answered Oct 23, 2013 at 13:20

ninjaPixel

6,3854 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ejlepoud · Accepted Answer · 2013-08-29 04:11:17Z

0

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'

answered Aug 29, 2013 at 4:11

ejlepoud

1,6082 gold badges12 silver badges5 bronze badges

Collectives™ on Stack Overflow

SQL Server query takes longer with parameter than with constant string

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related