Problem with Insert Exec in nested SP

[masri999]

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 .

[Erland Sommarskog]

(ma******@gmail.com) writes:

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx