Posted by masri999 on 07/24/06 07:40

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 .

• Next in forum: Deleting a column DEFAULT....
• Prev in forum: Re: Wanna create a table field name as 'Name'
• Thread view: Problem with Insert Exec in nested SP

[Reply to this message]