Advantage of stored procedure over SQL query
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: Advantage of stored procedure over SQL query

  1. #1
    Join Date
    Nov 2004
    Posts
    52

    Advantage of stored procedure over SQL query

    Does writing a stored procedure into, say, MS SQL Server 2000, and then executing the stored procedure from a programming language code yield an advantage over composing an SQL query from the programming language code and then firing the query from the programming language code?

  2. #2
    Join Date
    Apr 2005
    Location
    Norway
    Posts
    3,934

    Re: Advantage of stored procedure over SQL query

    Yes, it does.

    - It can improve performance.
    - It can reduce network traffic.
    - You don't have to implement your SQL statements into every application that talks to the database.

    - petter

  3. #3
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    Re: Advantage of stored procedure over SQL query

    Yes, a number of advantages.

    SP's in SQL Server are compiled when you save them, SQL sent to the server has to be interpreted before execution - there is a performance hit here (not a major amount, depends on the size of the statement).

    Sending large SQL strings over a network connection will increase network traffic and response times.

    By writing SP's, you are making your application more modular & scalable. If there is an error in the SP, all you have to do is change the TSQL and your application will be fixed. However, if the SQL is in the application itself, you would need to recompile and deploy to fix the error.

    My approach to this is to put as much logic as possible out of the vb application. Your vb app should be concered with displaying the data and allowing interaction, but all updates, deletes etc should be server side.

  4. #4
    Join Date
    Nov 2004
    Posts
    52

    Re: Advantage of stored procedure over SQL query

    Those were excellent answers, Dmorley and wildfrog.

    Now to extend my question, does writing a stored procedure at the backend and then executing it at the backend too prove advantageous over writing a SQL query at the back end and firing it at the backend?

    Your statement to the effect that SP's are precompiled makes me think. Do SQL queries not have execution plans too? One advantage might be the absence of line by line interpretation at run-time, but that happens to SQL queries only the first time until before they are cached, right? What's the mystery?

  5. #5
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    Re: Advantage of stored procedure over SQL query

    SQL Queries do have execution plans that are cached, but SQL Server will only reuse the execution plan if the SQL is an exact textual match against the cached plan.

    Here's an article about SQL caching, good read (relates to SQL7, but is still valid)SQL Caching

    There's a good example of 3 SQL statements, where one will generate a new execution plan. All of these SQL statements could have been handled by a single SP that uses parameters, and SQL Server would have a ready execution plan.

  6. #6
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    Re: Advantage of stored procedure over SQL query

    I will add to this, read this interesting blog posting, arguing the case for dynamic SQL. (I'm sure Frans would love to pick my arguments apart!)

    http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

    I'm not convinced myself, but there are arugments there that could help with some of your questions. Read the responses at the bottom, arguments both for & against!

  7. #7
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    651

    Re: Advantage of stored procedure over SQL query

    At my company, the DBA pushes for us to use Stored Procedures for security reasons (among the other reasons mentioned in the previous posts).

    For embedded SQL in your program, the user specified in the connection string would need permissions to query and/or modify the Tables used in the query.

    For a Stored Procedure, the user specified in the connection string only needs permissions to execute the Stored Procedure. That user does not need to have permissions to query and/or modify the table directly.
    I'd rather be wakeboarding...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center