Multiple SPs or single SP with multiple selects?

Multiple SPs or single SP with multiple selects?

Post by Jack Gardn » Tue, 13 Jul 1999 04:00:00



I have a stored procedure that returns 3 recordsets. For example:

create proc usp_Example
as
select * from table1_tbl

select * from table2_tbl

select * from table3_tbl

Now obviously the sp I have is much more complex and uses several
input parameters.  My question is whether or not it would be better to
encapsulate the select statements into stored procedures and then have
the main procedure call the other 3 procedures.

E.G.,

create proc usp_Example2
as
begin
        EXEC usp_get_table1
end

begin
        EXEC usp_get_table2
end

begin
        EXEC usp_get_table3
end

The reson I am thinking of this approach is that some of those
get_table sps get reused in several places - right now they exist as
blocks of code (select statements) within several different (larger)
stored procedures.

I'd like to take a modular approach, as long as there is a performance
cost.  Can someone give the pros and cons?  Would the first example
execute faster or the second?

this is on SQL 7.

Thanks,

Jack Gardner

 
 
 

Multiple SPs or single SP with multiple selects?

Post by Trevor Dwye » Wed, 14 Jul 1999 04:00:00


Hi Jack,

I have used both ways, with a slight tendacy to prefer the first method. It
really does depend on what & how you are querying. If a DSS enviroment then
you may find the first option will take too long, but if using the second
option then you run the risk of filling the transaction log.

There will be a huge difference if the recordsets were only relatively
small. then it probably would not matter which you choose. I have always
favoured using views to encapsulate as much of the business logic as I could
get away with and then use a 'calling' procedure to run the chain of events.

It would help to know a bit more but I thought I would mention how I
approached this problem.

--
Best Regards

Trevor Dwyer - SQL Server MVP


Quote:> I have a stored procedure that returns 3 recordsets. For example:

> create proc usp_Example
> as
> select * from table1_tbl

> select * from table2_tbl

> select * from table3_tbl

> Now obviously the sp I have is much more complex and uses several
> input parameters.  My question is whether or not it would be better to
> encapsulate the select statements into stored procedures and then have
> the main procedure call the other 3 procedures.

> E.G.,

> create proc usp_Example2
> as
> begin
> EXEC usp_get_table1
> end

> begin
> EXEC usp_get_table2
> end

> begin
> EXEC usp_get_table3
> end

> The reson I am thinking of this approach is that some of those
> get_table sps get reused in several places - right now they exist as
> blocks of code (select statements) within several different (larger)
> stored procedures.

> I'd like to take a modular approach, as long as there is a performance
> cost.  Can someone give the pros and cons?  Would the first example
> execute faster or the second?

> this is on SQL 7.

> Thanks,

> Jack Gardner


 
 
 

Multiple SPs or single SP with multiple selects?

Post by Jack Gardn » Wed, 14 Jul 1999 04:00:00


On Tue, 13 Jul 1999 03:09:46 +0100, "Trevor Dwyer"

<snip>

Quote:>e will be a huge difference if the recordsets were only relatively
>small. then it probably would not matter which you choose. I have always
>favoured using views to encapsulate as much of the business logic as I could
>get away with and then use a 'calling' procedure to run the chain of events.

>It would help to know a bit more but I thought I would mention how I
>approached this problem.

Given that the recordsets that are being returned are relatively small
(10 rows, 300 rows, 100 rows for the 3 selects respectively) and that
this sp is being used as part of an online transaction system
(virtually every user to this website will call this procedure several
times as it is part of a 'display component' that displays records
from database), I would assume the speed difference between the two
approaches would not be great enough to set up the possible
transaction log problems.

??  

Thanks,

Jack

 
 
 

1. Multiple SPs OR one SP w/ Multiple Recordsets (Part 2)

Hi,

There are some pretty knowledgable people here and I'd like to thank them
for sharing their wisdom and experience with us.

This was a question I brought up a while ago and Aaron and some other guys
were nice enough to answer my question. I'd like to go a little deeper this
time if I may.

What I learned from my previous question was that one big stored procedure
with multiple recordsets was more efficient than many smaller stored
procedures. I now have a stored procedure that produces 7 recordsets.
Performance-wise I'm pretty happy with though it's not really used in a very
demanding environment.

My question is: why is it that one SP is more efficient than many SPs? What
really eats up more system resources in terms of CPU cycles or memory or
both? Is it the connection to the DB? I'd really appreciate your
elaboration. Thanks.

Sam

2. LRU_PRESERVE on 9.30uc2e3 ?

3. More database bloating!

4. single select w/join versus multiple selects

5. US-TX-OK Good Oracle Talent

6. Problem Getting Recordset From Multiple Select Statement SPs

7. help: hot sql problem

8. Link multiple datagrids to a single record (Master\Detail Form with multiple details)

9. Sp being fed multiple values for a single variable

10. Combining multiple selected records into a single record

11. Select multiple top 5 record from single record set

12. urgent: returning multiple rows in single SELECT statment