Printable View
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?
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
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.
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?
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.
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!
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.