Benefits of stored procedures

[Brian Copeland]

I have been trying to figure something out with Stored Procedures, and was
hoping someone could clear something up for me. I have done a lot of
reading on them. Every book I have read said that because when a stored
procedure is saved/ran (different books, different wording) an execution
plan is created so that any further runnings of the sp are much quicker.

Okay, here lies my quandry. Does this mean that every single time the
stored procedure is run from now until doomsday will have this execution
plan 'in memory' the second I run the sp for the very first time? If so,
this would be the greatest thing since sliced pizza. Or does it mean that
when a session/connection is started and the sp is run that the execution
plan stays in memory, but only for hat session/connection. To me, this
would mean that any other person connecting to SQL server and running that
query would have to start all over as it were. It would also mean that
connections would have to be maintained through code (as a VB programmer)
rather than trying to make my code encapsulated and cut a connection
immediately before and immediately after I run the sp.

I will be migrating our existing code bases to SQL server early in the next
year, and I am trying to get all of the facts straight. The books I have
read are a little vague about this.

Thanks in advance.

Brian

[Kristin Piskulic]

Brian,
sp's stored without the recompile option at the start of them do execute
faster after the first time they are run. From what I understand, this is
because the sp is stored in it's compiled format once you run it the first
time. So every subsequent time it's run it is using the compiled version
of the stored proc. (that is, unless you have the recompile option set in
the beginning of the sp)

The biggest benefit to using sp's over embedded SQL in your VB code is that
you can eliminate extra hits to the SQL database across ODBC. Everytime
you execute a SQL statement from VB you're having to go across the ODBC layer
-- which is a time delay. When you can combine SQL statements into a sp
you will really see the benefits.

Typically we create connections to the necessary databases at the start of
the VB application and disconnect them upon exit.

Hope that helps.
Kristin Piskulic

As for "Brian Copeland" <bcopeland@frontlinefocus.com> wrote:
>I have been trying to figure something out with Stored Procedures, and was
>hoping someone could clear something up for me. I have done a lot of
>reading on them. Every book I have read said that because when a stored
>procedure is saved/ran (different books, different wording) an execution
>plan is created so that any further runnings of the sp are much quicker.
>
>Okay, here lies my quandry. Does this mean that every single time the
>stored procedure is run from now until doomsday will have this execution
>plan 'in memory' the second I run the sp for the very first time? If so,
>this would be the greatest thing since sliced pizza. Or does it mean that
>when a session/connection is started and the sp is run that the execution
>plan stays in memory, but only for hat session/connection. To me, this
>would mean that any other person connecting to SQL server and running that
>query would have to start all over as it were. It would also mean that
>connections would have to be maintained through code (as a VB programmer)
>rather than trying to make my code encapsulated and cut a connection
>immediately before and immediately after I run the sp.
>
>I will be migrating our existing code bases to SQL server early in the next
>year, and I am trying to get all of the facts straight. The books I have
>read are a little vague about this.
>
>Thanks in advance.
>
>Brian
>
>

[Jason Langston]

Brian,
First, veiled in your question seems to be a programming issue :
Do I create one connection and keep it open for the life of the application,
or do I use a more stateless approach and keep my connection open only long
enough to do my work (retrieve a recordset -disconnected - or execute a sp.)
My answer is never, ever leave a connection open longer than you need.
Connections eat up resources. For scalability and performance, close those
connections as soon as you're done.
Next, stored procedures - the best description I've found is in Inside SQL
Server 7.0 from MS Press (a must have for SQL DBA's or developers IMO). I
won't quote the whole thing, but it basically says "[the execution plan for
a sp] remains in the cache and is reused the next time **any connection**
executes the same procedure ..." blah, blah. The same book provides some
excellent details about how concurrent connections accessing the cached plan
are handled, and an idea of why the cached plan is really only temporary. I
think that answers your question.

HTH,
Jason L

Brian Copeland <bcopeland@frontlinefocus.com> wrote in message
news:3a0966e5$1@news.devx.com...
> I have been trying to figure something out with Stored Procedures, and was
> hoping someone could clear something up for me. I have done a lot of
> reading on them. Every book I have read said that because when a stored
> procedure is saved/ran (different books, different wording) an execution
> plan is created so that any further runnings of the sp are much quicker.
>
> Okay, here lies my quandry. Does this mean that every single time the
> stored procedure is run from now until doomsday will have this execution
> plan 'in memory' the second I run the sp for the very first time? If so,
> this would be the greatest thing since sliced pizza. Or does it mean that
> when a session/connection is started and the sp is run that the execution
> plan stays in memory, but only for hat session/connection. To me, this
> would mean that any other person connecting to SQL server and running that
> query would have to start all over as it were. It would also mean that
> connections would have to be maintained through code (as a VB programmer)
> rather than trying to make my code encapsulated and cut a connection
> immediately before and immediately after I run the sp.
>
> I will be migrating our existing code bases to SQL server early in the
next
> year, and I am trying to get all of the facts straight. The books I have
> read are a little vague about this.
>
> Thanks in advance.
>
> Brian
>
>