July 11, 2011 at 1:39 pm
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
July 11, 2011 at 2:39 pm
Please post the actual execution plans for both queries.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 11:30 pm
Is this a large table? Is one plan being stored in the cache with not optimal estimates?
Try clearing down the cache , updating statistics and rerunning or RECOMPILING
Jack Vamvas
sqlserver-dba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply