Query much slower when variables are used

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query much slower when variables are used

Author

Topic

MyronSchroner
Starting Member

3 Posts

Posted - 2010-01-11 : 20:05:10

Hi,

Why would a query take longer when variables are used? I've checked that the type of variable declared matches the variable type in the table.

Both return the same results.

-------Query 1------------ takes about a second
SELECT COUNT(*)
FROM view_a
WHERE ChartID = '100005' AND ClientID = 27

-------Query 2------------ takes about a minute
DECLARE @ChartID VARCHAR(15)
DECLARE @ClientID INT
SET @ChartID = '100005'
SET @ClientID = 27
SELECT COUNT(*)
FROM view_a
WHERE ChartID = @ChartID AND ClientID = @ClientId

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-11 : 20:10:36

It's known as parameter sniffing. You are getting different execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 01:02:25

more details here
http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-12 : 04:30:45

Is your query with variables running in a stored procedure?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-12 : 04:32:45

If most of your queries use same value for ClientId or ChartId, you can add the query hint "optimize for"
or, add the "WITH RECOMPILE" option at the end of the query.

N 56°04'39.26"
E 12°55'05.63"

MyronSchroner
Starting Member

3 Posts

Posted - 2010-01-12 : 14:18:06

Hi all, and thanks for the input. The references you mentioned to parameter sniffing are helpful, but seemed to be focused arouind stored procedures. This isn't in a sproc, it's just a query. Would parameter sniffing still apply? And more importantly, how do you get around it in a query? I understand how to do it in a sproc (from the examples you pointed me to), but not in a simple query.

Thanks!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 14:29:59

You could try and build the sql string dynamically creating a Exec () statement which in essense will have the values define by your variables and see if that improves things.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-12 : 14:34:10

in addition to all of the other comments do you have indexes on the underlying table's fields ChartID and ClientID ? maybe that might help speed things up?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 14:39:02

Here was the dynamic build...try it out I guess...

Create Table  #t1 (Name Varchar(20), ChartID varchar(6), ClientID Int)
Insert #t1
Select 'A','100005', 27 Union
Select 'B','100006', 27 Union
Select 'C','100005', 27 Union
Select 'D','100005', 28 

Select * from #T1
Declare @chart varchar(6)
Declare @ID int

Set @chart = '100005'
Set @ID = 27

Declare @sql as Varchar(max)
Set @sql = 'SELECT COUNT(*)
FROM #t1 WHERE ChartID = '+ @chart + ' AND ClientID = '+ cast(@ID as varchar(2)) + ''
Exec (@sql)

MyronSchroner
Starting Member

3 Posts

Posted - 2010-01-12 : 14:59:34

Creating a dynamic sql statement worked and is fast. Thanks! :-)

For reference, do you know why it would be faster to use variables to create a dynamic sql statement and run that than to use variables in a standard sql statement?

Some additional notes:
Looked into this a bit more and found that I could use RECOMPILE at the query level. I tried it with OPTION(RECOMPILE) and with OPTION(OPTIMIZE FOR (@ChartId = '100005')). Neither changed anything.

I set STATISTICS PROFILE ON. The first query had 12 rows and the Estimate Rows was about 3. The second query had 125 rows and the Estimate Rows was 226,000.

I do have the indexes on those columns. The first query runs quickly.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 15:53:40

The variables were only used to build the sql string, in essense you ran the query just like in Query 1 of your original post. Therefor it took advantage of some added efficiencies and compiled just like your original. You, in effect, removed the variables from the SQL statement.

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 02:41:36

I would be interested if recording:


Declare @sql as Varchar(max)
Set @sql = 'SELECT COUNT(*)
FROM #t1 WHERE ChartID = '+ @chart + ' AND ClientID = '+ cast(@ID as varchar(2)) + ''
Exec (@sql)

to use sp_ExecuteSQL instead suffered from the same parameter sniffing / recompilation problem, or not.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources