Multiple stored procedures ,same tables

Hi,

I am using multiple stored procedures which are using same set of tables at a time .

As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.

My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins

where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you

Raj Deep.A

Create a parent stored procedure then create your temp tables there. Then you just call your other stored procedures. They should then be able to use the temp tables you've created in the parent stored procedure.

e.g.

Code Snippet

create procedure usp_parent

as

select *

into #tmp

from sysobjects

exec usp_child1

exec usp_child2

go

create procedure usp_child1

as

select 'usp_child1',*

from #tmp

go

create procedure usp_child2

as

select 'usp_child2',*

from #tmp

go

exec usp_parent

go

drop procedure usp_child1,usp_child2,usp_parent

go

|||

If you have SQL 2005 and your application is critical I should mirror the database (with database mirroring feature); i'll try creating a database snapshot too.

Then i should divide the execution some of sp's on a database some of other.

|||

Hi Oj,

If you know about the reports,for each select statement i want to show i need a seperate stored procedures or same stored procedure should be called multiple times with different parameters which returns different select statement based on the parameters i passed.

i cant avoid using multiple stored procedures. i think i should have an option where can i use some feature of sql server for not creating locks.

Thnk you

Raj Deep.A

|||

You can take a look at NOLOCK hint in book online.

e.g.

Code Snippet

select *

from tb with(nolock)

where col1=123