Re: Does nesting stored procedures make sense to increase performance?
- From: "Bob Beauchemin" <no_bobb_spam@xxxxxxxxxxxxx>
- Date: Mon, 18 May 2009 13:55:38 -0700
Hi Sam,
Reducing client or middle-tier round trips to the database is a good idea in that it will reduce network traffic. Your milage in improving performance will depend on how close the middle-tier is to the database. In SQL Server 2000 (and earlier versions), because recompiles of query plans are done on a per-procedure basis, it's helpful some execute one SQL statement per procedure as well. In SQL Server 2005-2008 query plans are recompiled on a per-statement basis (not per-procedure) and statement-per-procedure does not assist in reducing recompiles.
If you're going to combine many calls into a single call, you'll need to add robust error handling to the "driver" procedure so as not to call procedures 2,3, and 4 if procedure 1 fails, etc. You'd need to replicate your existing client/middle-tier code's concept of success/failure results quit the batch of procedures on a failure and the return the results/return code that the client/middle-tier sees in the original. This will likely mean changes to the data access code as well.
Hope this helps,
Bob Beauchemin
SQLskills
"Sam" <Sam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:742C3C1C-4E9D-40C9-9C75-A73AC8640D67@xxxxxxxxxxxxxxxx
Hi,
I've recently became aware of nesting stored procedures in SQL Server. In my
public facing application, every page makes 2-4 database calls to retrieve
different pieces of the information. I know that database calls are expensive.
With that said, does it make sense to create a single stored procedure that
executes multiple stored procedures so that I can make a single database call
-- and call that one stored procedure? Would this make sense? Would this help
improve performance?
NOTE: If I don't do this, I'd be calling those stored procedures that will
be nested in that single SP, one by one hence making multiple database calls.
--
Thanks,
Sam
.
- Follow-Ups:
- References:
- Prev by Date: Re: Trigger for Detection
- Next by Date: Re: Does nesting stored procedures make sense to increase performa
- Previous by thread: Re: Does nesting stored procedures make sense to increase performa
- Next by thread: Re: Does nesting stored procedures make sense to increase performa
- Index(es):
Relevant Pages
|
