Inserting a stored procedure result set into a temporary table 
Author Message
 Inserting a stored procedure result set into a temporary table

I have run into a performance issue when trying to insert the result
set from a stored procedure into a temporary table.  I am running the
following statement.

create table  #temp
(field_1                char(80)        NULL,
 field_2                int             NULL,
 field_3                int             NULL,
 field_4                int             NULL,
 field_5                decimal(16, 2)  NULL)

insert into #temp
exec stored_procedure   param1, param2, param3

drop table #temp

When stored_procedure is executed and does not populate a table the
execution time is 11 seconds.  When the above script is run the
execution time is 45 seconds.  Does anyone know of performance issues
with inserting stored procedure result sets into tables?



Sat, 29 May 2004 05:41:14 GMT
 Inserting a stored procedure result set into a temporary table

sarah,

a few things:

    2. writing result to disk (i.e. #tmp is created in tempdb) can be costly
    3. your stored procedure is not optimized

-oj


Quote:
> I have run into a performance issue when trying to insert the result
> set from a stored procedure into a temporary table.  I am running the
> following statement.

> create table  #temp
> (field_1 char(80) NULL,
>  field_2 int NULL,
>  field_3 int NULL,
>  field_4 int NULL,
>  field_5 decimal(16, 2) NULL)

> insert into #temp
> exec stored_procedure param1, param2, param3

> drop table #temp

> When stored_procedure is executed and does not populate a table the
> execution time is 11 seconds.  When the above script is run the
> execution time is 45 seconds.  Does anyone know of performance issues
> with inserting stored procedure result sets into tables?



Sat, 29 May 2004 05:48:18 GMT
 Inserting a stored procedure result set into a temporary table
Sarah,

I hope this is a dumb question, but when the temp table is populated, just
how many rows are being added. If you're adding hundreds of thousands of
rows, then perhaps that's the reason   ;-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I have run into a performance issue when trying to insert the result
> set from a stored procedure into a temporary table.  I am running the
> following statement.

> create table  #temp
> (field_1 char(80) NULL,
>  field_2 int NULL,
>  field_3 int NULL,
>  field_4 int NULL,
>  field_5 decimal(16, 2) NULL)

> insert into #temp
> exec stored_procedure param1, param2, param3

> drop table #temp

> When stored_procedure is executed and does not populate a table the
> execution time is 11 seconds.  When the above script is run the
> execution time is 45 seconds.  Does anyone know of performance issues
> with inserting stored procedure result sets into tables?



Sat, 29 May 2004 11:18:03 GMT
 Inserting a stored procedure result set into a temporary table

2000.  I already tried that.  =)

Ok, this may be a silly question, but even if the stored procedure is
not optimized should it take 4 times as long to load the data to the
table as it does to just return the result set?  It seems to me that
if the procedure was not optimized then the bulk of my time would be
in the execution of the procedure, not in the insert.  Like I said,
probably a silly question.

Quote:

> sarah,

> a few things:

>     2. writing result to disk (i.e. #tmp is created in tempdb) can be costly
>     3. your stored procedure is not optimized

> -oj



> > I have run into a performance issue when trying to insert the result
> > set from a stored procedure into a temporary table.  I am running the
> > following statement.

> > create table  #temp
> > (field_1 char(80) NULL,
> >  field_2 int NULL,
> >  field_3 int NULL,
> >  field_4 int NULL,
> >  field_5 decimal(16, 2) NULL)

> > insert into #temp
> > exec stored_procedure param1, param2, param3

> > drop table #temp

> > When stored_procedure is executed and does not populate a table the
> > execution time is 11 seconds.  When the above script is run the
> > execution time is 45 seconds.  Does anyone know of performance issues
> > with inserting stored procedure result sets into tables?



Sun, 30 May 2004 01:09:43 GMT
 Inserting a stored procedure result set into a temporary table
Nope, not a dumb question, the amount of data being loaded is
extremely small.   The example below was loading 28 rows.
Quote:

> Sarah,

> I hope this is a dumb question, but when the temp table is populated, just
> how many rows are being added. If you're adding hundreds of thousands of
> rows, then perhaps that's the reason   ;-)

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I have run into a performance issue when trying to insert the result
> > set from a stored procedure into a temporary table.  I am running the
> > following statement.

> > create table  #temp
> > (field_1 char(80) NULL,
> >  field_2 int NULL,
> >  field_3 int NULL,
> >  field_4 int NULL,
> >  field_5 decimal(16, 2) NULL)

> > insert into #temp
> > exec stored_procedure param1, param2, param3

> > drop table #temp

> > When stored_procedure is executed and does not populate a table the
> > execution time is 11 seconds.  When the above script is run the
> > execution time is 45 seconds.  Does anyone know of performance issues
> > with inserting stored procedure result sets into tables?



Sun, 30 May 2004 01:11:00 GMT
 Inserting a stored procedure result set into a temporary table
Sarah,

Follow up dumb question  :-)

If you just execute the stored procedure ... comment out the create table
and the insert ... what is the running time?

You might also use SQL Server Profiler to check whether the loading of the
table with data is causing a recompilation of the stored procedure ... in
fact, that's my guess ... as I now proceed to make a fool out of myself  :-)

Documentation on SQL Server Profiler can be found in the SQL Server Books
Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Nope, not a dumb question, the amount of data being loaded is
> extremely small.   The example below was loading 28 rows.




Quote:
> > Sarah,

> > I hope this is a dumb question, but when the temp table is populated,
just
> > how many rows are being added. If you're adding hundreds of thousands of
> > rows, then perhaps that's the reason   ;-)

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I have run into a performance issue when trying to insert the result
> > > set from a stored procedure into a temporary table.  I am running the
> > > following statement.

> > > create table  #temp
> > > (field_1 char(80) NULL,
> > >  field_2 int NULL,
> > >  field_3 int NULL,
> > >  field_4 int NULL,
> > >  field_5 decimal(16, 2) NULL)

> > > insert into #temp
> > > exec stored_procedure param1, param2, param3

> > > drop table #temp

> > > When stored_procedure is executed and does not populate a table the
> > > execution time is 11 seconds.  When the above script is run the
> > > execution time is 45 seconds.  Does anyone know of performance issues
> > > with inserting stored procedure result sets into tables?



Sun, 30 May 2004 06:09:44 GMT
 Inserting a stored procedure result set into a temporary table
The execution time is 11 seconds without the create and insert statements and 45 seconds with the create and insert statements.  Thank you for the idea of checking out the Profiler, I will certainly do that.

Sarah

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 30 May 2004 06:18:59 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Creating a Temporary Table from a Stored Procedure Result Set

2. Inserting Stored Procedure Results into a Temporary Table

3. Storing the result of a stored procedure call in a temporary table

4. Insert procedure result to temporary table

5. Stored procs-Result Sets from Global Temporary Tables

6. Results from stored procedure to temporary table?

7. Passing a result set from one stored procedure to another stored procedure

8. temporary tables in stored procedures/parameter views vs stored procedures

9. Stored procedure's result set INTO table

10. Returning a result set from a table using a stored procedure

11. Inserting into table stored procedure result

12. Send result set to a temporary table


 
Powered by phpBB® Forum Software