Re: Does nesting stored procedures make sense to increase performa
- From: Sam <Sam@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 18 May 2009 13:56:11 -0700
sloan,
Thanks for your response. I agree that it's not desirable and glad to hear
that it's OK to nest SPs into one.
I like the idea of calling individual SPs from one because it gives me the
ability to maintain each stored procedure individually. So if I make some
modifications in the stored procedure, this only affects that single SP.
I assume, by nesting SPs into one, I'd be returning multiple recordsets in
one stored procedure.
--
Thanks,
Sam
"sloan" wrote:
.
//every page makes 2-4 database calls to retrieve
different pieces of the information. I know that database calls are
expensive.//
That is less than desired.
Your optimal goal is "one page, one db hit"
So there is nothing wrong with nesting......I would lean toward Select
statements........
If you do other items other than Select, just be careful.
Here is a typical but simple example that is acceptable, and better than
calling each (sub) usp seperately.
But be careful, because what will happen is that you'll start putting a
bunch of "flag" parameters in the sub usp's and then it gets confusing.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[uspOrganizationGetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspOrganizationGetAll]
GO
CREATE PROCEDURE [dbo].[uspOrganizationGetAll]
AS
BEGIN
SET NOCOUNT ON
exec [dbo].[uspDepartmentGetAll]
exec [dbo].[uspEmployeeGetAll]
exec [dbo].[uspJobTitlesGetAll]
SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.uspOrganizationGetAll TO $(DBUSERNAME)
GO
"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
- References:
- Prev by Date: Re: Does nesting stored procedures make sense to increase performance?
- 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 performance?
- Index(es):
Relevant Pages
|
Loading
