Parameterized query performance

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 2008 Forums

Transact-SQL (2008)

Parameterized query performance

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-07-11 : 15:40:13

Guys

I am having a weird where the same query with parameterized vs non parameterized for the same input parameter, the parameterized query is taking 3X times longer
and this is consistent with any kind of input parameters, I am not sure why the SQL Engine is taking more execution time for parameterized query though the query
plan is same.

Non Parameterized < 1sec response time

SELECT Top(500) DISPLAYNAME AS 'Name',lastdate,identityid,SEX
FROM view1 WHERE 1=1
WHERE 1=1 AND LAST_NAME LIKE 'matt%'

Parameterized > 5 sec response time

declare @parm1 varchar(100) = 'matt%'
SELECT Top(500) DISPLAYNAME AS 'Name',lastdate,identityid,SEX
FROM view1 WHERE 1=1 AND LAST_NAME LIKE @parm1

Any suggestions or inputs would help

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-11 : 16:47:56

Are you sure the plan was the same? Be sure to check the actual rather than the estimated query plan:
set showplan_text on

Be One with the Optimizer
TG

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-07-12 : 09:16:06

That is correct with showplantext on the execution plans are excatly the same. I am wondering if the query plan from the parameterized queries is not being cached. Is that possible?

Any suggestions/inputs would help.

Thanks

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-07-12 : 09:21:53

One more thing to notice is statistics for parameterized query is different from non parameterized query

Parameterized Query

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 49578 ms, elapsed time = 48576 ms.

SQL Server Execution Times:
CPU time = 49578 ms, elapsed time = 48576 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Non Parameterized query

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 23277 ms, elapsed time = 5795 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-07-12 : 09:40:11

One more thing I noticed is with parameterized query that is different from non parameterized query is worktable scan count and logical reads. If query and the corresponding execution plan is exactly the same why this would be different.

Table 'Worktable'. Scan count 27601, logical reads 394329, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-12 : 10:59:13

I believe this is a case of "parameter sniffing" - it has to do with the statistics kept and the optimize making plan decisions. One possible work around is to declare a local variable, set that variable to the value of your parameter and use the local variable in your statement.

I'm not sure why your plan shows the same - I would have expected that the actual plans differ.

Be One with the Optimizer
TG

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-07-12 : 11:47:02

Thanks for the response and this is what I did to fix the issue (atleast it seems the issue is fixed)

The MAXDOP was 0 on 16porcessor instance changed it to 8 and that did the trick.

Also is it safe to assume that parametrized SQL query plan is not cached as supposed to non parametrixed and hence we didnt notice the effect of 0 MAXDOP while using the non parametrized queries throughout the application.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources