Re: Does nesting stored procedures make sense to increase performa
- From: Sam <Sam@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 18 May 2009 14:24:05 -0700
Bob,
Thanks for your response.
In my case, calling the individual stored procedures will not depend on
certain conditions being met. I have to call all of them so maybe the
"nested" is not the right way to phrase it. Maybe I should say, calling other
procedures from one.
An example may explain this better. The application is an ASP.NET
application that utilizes lots of user controls which are great for
encapsulating functions but each user control calls the database so when you
look at the bigger picture, each page call which contains multiple user
controls generates multiple database calls.
A good example is project info. In the project info page, I may have 3 user
controls:
User Control 1 provides project team info by calling stored procedure 1.
User Control 2 provides project history info by calling stored procedure 2.
and so on.
I'm trying to reduce the DB calls by "consolidating" stored procedure calls
into 1.
You are right though. This will require me to re-think the way I call the
database at DAL because currently eacy user control acts independently. I'll
have to figure out a way to centralize DB calls and data distribution to
individual user controls.
--
Thanks,
Sam
"Bob Beauchemin" wrote:
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: Deterministic dates
- Next by Date: Re: Deterministic dates
- Previous by thread: Re: Does nesting stored procedures make sense to increase performance?
- Next by thread: Re: Does nesting stored procedures make sense to increase performa
- Index(es):
Relevant Pages
|
