Caching of inline queries

Hi,
I am trying to evaluate the benefits of writing stored procedures over
using inline SQL code using ADO.net and SqlServer. I am coming from an
oracle background, and am trying to understand under what
circumstances SqlServer will recompile my query, and when it will use
a pre-compiled version. The Oracle term for this behavior is
'bind-variable'. The only Oracle/bind-variable related information I
found here was
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=e0dtNgY%24CHA.2572%40TK2MSFTNGP11.phx.gbl
It seems to indicate that in order to avoid recompilation that I could
use stored procedures, format my queries using sp_executesql, or use
the prepared statement in my command object and reuse the object.
I am confused because by examining the syscacheobjects, it seems my
parameterized query is cached and reused automatically.
Here is what I am doing to test this. I have some code that looks like
this:
SqlConnection MyConnection = new SqlConnection(ConnectionString);
MyConnection.Open();
SqlCommand MyCommand = new SqlCommand("SELECT GStudentId, Lastname,
FirstName FROM student WHERE GStudentId=@.StudentId", MyConnection);
MyCommand.Parameters.Add("@.StudentId", _GStudentId);
SqlDataReader MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
I call this code on a simple web page, which lets me easily execute it
again and again.
I then do the following query inside Query Analyzer:
select usecounts, sql from syscacheobjects
where sql like '%SELECT GStudentId, Lastname, FirstName FROM student
WHERE GStudentId=@.StudentId%'
As I execute the page over and over the use count goes up.
Syscacheobjects actually returns two rows, one with a cached object
type of ?Executable Plan' (this is the one with the ?incrementing
usecounts'), and one with a type of ?Compiled Plan'. What are these
each used for?
Doesn't this mean that Sql Server is caching the query? I am
recreating the command object from scratch each time, yet it seems to
use the same "cached execution plan".
I am very happy with this behavior, yet it is in contradiction to the
other information I was able to find.
Can someone validate?
Thanks!
DaveSQL Server will do SIMPLE parameter substitution to reuse an ad-hoc plan.
That's probably what's happening in your example, but this will only happen
in trivial cases. You're better off with a proc us sp_executesql.
This paper is a good starting point for understanding SQL Server
behaivior...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"HaukiDog" <HaukiDog@.hotmail.com> wrote in message
news:6d3bc6d9.0311241301.58817947@.posting.google.com...
> Hi,
> I am trying to evaluate the benefits of writing stored procedures over
> using inline SQL code using ADO.net and SqlServer. I am coming from an
> oracle background, and am trying to understand under what
> circumstances SqlServer will recompile my query, and when it will use
> a pre-compiled version. The Oracle term for this behavior is
> 'bind-variable'. The only Oracle/bind-variable related information I
> found here was
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=e0dtNgY%24CHA.2572%40TK2MSFTNGP11.phx.gbl
> It seems to indicate that in order to avoid recompilation that I could
> use stored procedures, format my queries using sp_executesql, or use
> the prepared statement in my command object and reuse the object.
> I am confused because by examining the syscacheobjects, it seems my
> parameterized query is cached and reused automatically.
> Here is what I am doing to test this. I have some code that looks like
> this:
> SqlConnection MyConnection = new SqlConnection(ConnectionString);
> MyConnection.Open();
> SqlCommand MyCommand = new SqlCommand("SELECT GStudentId, Lastname,
> FirstName FROM student WHERE GStudentId=@.StudentId", MyConnection);
> MyCommand.Parameters.Add("@.StudentId", _GStudentId);
> SqlDataReader MyReader => MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
> I call this code on a simple web page, which lets me easily execute it
> again and again.
> I then do the following query inside Query Analyzer:
> select usecounts, sql from syscacheobjects
> where sql like '%SELECT GStudentId, Lastname, FirstName FROM student
> WHERE GStudentId=@.StudentId%'
> As I execute the page over and over the use count goes up.
> Syscacheobjects actually returns two rows, one with a cached object
> type of 'Executable Plan' (this is the one with the 'incrementing
> usecounts'), and one with a type of 'Compiled Plan'. What are these
> each used for?
> Doesn't this mean that Sql Server is caching the query? I am
> recreating the command object from scratch each time, yet it seems to
> use the same "cached execution plan".
> I am very happy with this behavior, yet it is in contradiction to the
> other information I was able to find.
> Can someone validate?
> Thanks!
> Dave