Problem with Insert Exec in nested SP

Hello,

I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .

Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .

I have defined a # table in Parent SP and calling a Child SP like this
insert into #temp exec childsp .....

Child SP has Select * from local # temp table ( local to child SP
) as the last statement .

When number of records are less ( around 1000 - 5000) Parent SP
executes but slow .

When the Child SP returns higher number of rows ( 1,00,000 or more )
the SP will be running for hours with out completion .
Although executing the child SP , with exec ChildSP ... with same
parameters it is completed in 2 mins for 3,00,000 rows .

Resolution : - Define a temp table (say #tempChild ) in the Parent SP
..
In the Child SP instead of select * replace with insert into
#tempChild select * from ...

Also note that this problem is not noticed in SQL 2000 Server with SP4
..

This may be due to SP executing in implicit transactions .(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .
>
Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .
>
I have defined a # table in Parent SP and calling a Child SP like this
insert into #temp exec childsp .....
>
Child SP has Select * from local # temp table ( local to child SP
) as the last statement .
>
When number of records are less ( around 1000 - 5000) Parent SP
executes but slow .
>
When the Child SP returns higher number of rows ( 1,00,000 or more )
the SP will be running for hours with out completion .
Although executing the child SP , with exec ChildSP ... with same
parameters it is completed in 2 mins for 3,00,000 rows .
>
Resolution : - Define a temp table (say #tempChild ) in the Parent SP
.
In the Child SP instead of select * replace with insert into
#tempChild select * from ...

For further discussion on the problem of passing data between stored
procedures, there is an article on my web site:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx