July 2, 2008 at 2:01 am
Hi everybody,
Something I don't understand here:
Let's say I run these 3 queries:
select * from mytable where id = 1;
select * from mytable where id = 3;
select * from mytable where id = 3;
When I query the sys.syscacheobjects
view it shows 3 rows, i.e., one for each of the above queries.
However, I thought that the cached plan would be the same since I'm just changing a literal value...at least that's what I read in BOL...
What am I not understanding?
July 2, 2008 at 2:10 am
Use parameters :). In the above you have three different statements, with different execution plans.
DECLARE @param int
SET @param = 1
EXEC sp_executesql
N'select * from mytable where id = @p1',
N'@p1 INT',
@param
In the above code the select statement will result in a single cached plan, and you can reuse it by calling it with different parameters.
Regards,
Andras
July 2, 2008 at 2:13 am
You can read more on this on http://msdn.microsoft.com/en-us/library/ms175580.aspx
Regards,
Andras
July 2, 2008 at 4:03 am
Thanks for the link 🙂
OK, still a bit confused:
From this link : http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E5D
It says "For example, the query can be submitted under "set showplan_xml on" mode. If the resulting showplan contains such placeholders as @p1 and @p2, then the query has been auto-parameterized;"
When I run showplan_xml on the query I get something like this:
...
....
Doesn't this mean the query was parameterized? I thought so, but then when I looked in the cachesql, the literal constant was still there.
I thought then the sql in the cache would agree with the showplan_xml and be like "select * from mytable where id = @0"
Maybe I'm misunderstanding the syscacheobjects view...the sql column contains the adhoc text sent down...maybe that's not the actual query plan...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply