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?

> ie, to have a chain of Procs rather than a chain of queries.

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

<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

Jim Tottser wrote:
>
> 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.

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

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

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