Re: queries Vs Stored Procs
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 01/01/05
- Next message: Kenny M.: "Thinking about code or SP"
- Previous message: Bob Barrows [MVP]: "Re: queries Vs Stored Procs"
- In reply to: Jim Tottser: "Re: queries Vs Stored Procs"
- Next in thread: Erland Sommarskog: "Re: queries Vs Stored Procs"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 1 Jan 2005 17:04:55 +0100
> Bob is exactly right. However, there is an exeption to the many
> queries in one stored procedure rule. Any time you need to use a
> conditional branching statement to execute different queries you
> should put each branch into its own stored procedures. The reason for
> this is that the compiler will choose an execution path down only one
> branch.
Just a slight elaboration, in case somebody is interested:
The optimizer doesn't really know or care about the branching in the code. The optimizer sees all
DML statements and also knows the values of the procedure parameters. Based on that, it optimizes
all DML statements, whether or not they later will be executed.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "Jim Tottser" <jtottser@ptd.net> wrote in message news:l2fdt01ncqlngrntr9bteirp88vn27uqo6@4ax.com... > On Sat, 1 Jan 2005 08:46:58 -0500, "Bob Barrows [MVP]" > <reb01501@NOyahoo.SPAMcom> wrote: > >>Nickl wrote: >>> I often use a chain of queries to produce a final output. Eg to >>> prepare aggregate data, or to set up generic queries which are then >>> used in many other queries to simplify things. >>> My question is this; how much advantage performance wise will I get >>> if I make those sub-queries stored procedures and then call them from >>> other stored procedures? >> >>I would say you are looking at a major performance improvement. Marshalling >>data across processes is expensive and time-consuming. Each trip to the >>database from your client application incurs that penalty, so it only makes >>sense to minimize the number of trips to the database whenever possible. >>Also, stored procedures are pre-parsed and compiled, so that is another >>operation that is avoided when you switch to using stored procedures. >> >>> ie, to have a chain of Procs rather than a chain of queries. >> >>You need to get yourself out of the paradigm of one query/one procedure. You >>can put multiple queries into a single stored procedures. This is called >>"encapsulation". There is no reason to have a chain of stored procedures >>unless some of the queries need to be used by other processes. >>> >>> Do I get any benefit by starting Procs earlier in the chain (if there >>> is one), or is it ok to have one proc at the end that references the >>> the next query, which references the next query etc. >> >>Again, a single procedure can execute and use the results from several >>queries, so tere is no reason to think about creating more than one >>procedure. Again, minimizing the trips out of process can only have a >>beneficial impact on performance. >> >>Look at this: >>http://www.aspfaq.com/show.asp?id=2201 >> >>Bob Barrows > > Bob is exactly right. However, there is an exeption to the many > queries in one stored procedure rule. Any time you need to use a > conditional branching statement to execute different queries you > should put each branch into its own stored procedures. The reason for > this is that the compiler will choose an execution path down only one > branch. Using seperate stored procedures allows SQL Server to create > seperate execution plans for each branch. > >
- Next message: Kenny M.: "Thinking about code or SP"
- Previous message: Bob Barrows [MVP]: "Re: queries Vs Stored Procs"
- In reply to: Jim Tottser: "Re: queries Vs Stored Procs"
- Next in thread: Erland Sommarskog: "Re: queries Vs Stored Procs"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
