When to use Stored Procedures
-
Basically if i have a simple SQL statement in an app i should ALWAYS create a stored procedure for it rather than send it to the DB as a query.
- One reason is security reasons
- SP are already compiled
I just want some confirmation taht i should always use SP if possible for all queries that are frequently used.
Thanks!
-
other reasons:
- they create an abstraction layer for the Client side
- They give you more flexibility in maintanace changes
- They are very efficient when called repaeatedly with different parameter values
- They modularize your code
- They avoid round trips when several statements can be combined
... among others 😉
* Noel -
Thanks very much! As usual Noel!
-
To my knowlegde SP's are not compiled, or am i mistaken about the following piece of BOL:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.
Disadvantages of SP's are:
- difficult version control
- changes to SP's can be disrupted by an restore
- difficult exception handling
I'm not saying: don't use SP's. But keep the disadvantages in mind.
-
There is a place where you almost always want a stored procedure and that is in retrieving data from a data warehouse/dimensional model where there may be a number of JOINs, need to create temp tables for data redefinition, etc. They outperform views dramatically AND give you much more possibilities in terms of functionality.
-
True, SPs are not compiled but they can and frequently do use a previously created execution plan. Inline code does not.
And with regard to source control, granted it takes discipline with pre SQL 2005 to maintain, but SQL 2005 integrates with source control so that argukment goe away.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
I find that very interesting guys! My book MS SQL Server 2000 Bible says under Stored Procedures page 403: "Store procedures are compiled and the fastest possible means of executing a batch or query."
-
If that is all it says that it is an extreme simplification, and not always correct.
-
True, SPs are not compiled but they can and frequently do use a previously created execution plan. Inline code does not.
Not correct, ad-hoc SQL can very well reuse previously compiled plans. See the BOL extract that Gimli has in his post.
-
As I referred to previously, I work in a SQL datawarehouse/analysis/reporting environment where it's ALWAYS advantageous, both from flexibility and a performance point of view, to use a stored procedure. We have available to us what are called "reporting views" that include the JOINs from the fact table to a multitude of dimensional tables so that a novice Crystal developer can easily generate reports. BUT when the fact tables have several million rows and there are 30,40 and in some cases, 50+ joins to dimensional tables, then is just as easy (from my point of view) to create a stored procedure that provides the minimum number of joins from the fact to the dimensional tables.
Whether or not the SP is compiled is immaterial in my situation. It still retrieves data faster.
-
In my experience over 90% of the people I talk about SP's think that they are pre-compiled on creating and ad-hoc SQL-plans can't be reused (sometimes even writers of books). Where are the guys from Microsoft to educate us? Is it a hidden feature?
I think the biggest performance advantage of a SP is to avoid round trips when several statements are combined. Putting only 1 simple select in an SP has to my knowlegde no performance advantage.
-
That was all it said on that page. And i do understand that a book for novices, like myself, tends to over simplify.
The statement, i quoted earlier, was the first of bullets on the page under "As server-side code, stored procedures offer several benefits:" Then it states that as teh first bulleted point.
Later on page 405 under "Compiling Stored Procedures," it says "Compiling a stored procedure is an automatic process. SPs compile and are stored in memory the first time they are executed. If the server reboots, all the compiled SPs are lost. They are again compiled when they are called."
I'm only stating these things because it seems an interesting contradiction and it raises the question: How are SPs treated?
My assumption is that this question would take more than one line or two to explain the difference and i'm not that interested. 🙂 Novices can't afford to care, we have enough to learn.
-
You are comparing a select in a SP with no views to an ad-hoc select wich has views. That's comparing appels and pears (dutch saying). If you had a way of making a select without the views by the Crystal developer it would perform just as good. I know an application (cosa workflow) that uses wat they call predefined queries. The developer or user can fill in some parameters and the application fires the statement (with a lot of joins). It's not an advantage to put this in a SP.
-
I'm referring to other books.
But I'm curious. Does the book say that ad-hoc sql-statements are also compiled.
-
Gimli, he does seem to differentiate on this point.
( I had explained a little more but every now and them my info gets lost when i press post reply.)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply