DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Benefits of stored procedures

  1. #1
    Brian Copeland Guest

    Benefits of stored procedures

    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



  2. #2
    Kristin Piskulic Guest

    Re: Benefits of stored procedures


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



  3. #3
    Jason Langston Guest

    Re: Benefits of stored procedures

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




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center