470,636 Members | 1,598 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Table Spool in simple INSERT...SELECT causing performance problems


I have a table which has a few million records. It has an IDENTITY
column that serves as the primary key. In a part of our application
here, a previous record may need to be copied as a new row. Within a
stored procedure exists SQL like:

INSERT INTO [My_DB].[dbo].[My_Table] (col1, col2, col3, ...)
SELECT @var1, col2, col3, ...
FROM My_Table
WHERE my_id = @my_id --This column is the IDENTITY column PK

For some reason, this query is using a Table Spool, and I can't figure
out why. I know the definition of a Table Spool (records saved in
tempdb so that rewinds can use the temp table), but I don't understand
why it is being used here or how to prevent it.

I've tried rebuilding all of the indexes on the table and that did not

I tried creating a scaled-down example that reproduces the problem,
but I could not and it's not practical to post the table definition
and data to reproduce the problem here. If there is further
information that would help someone determine the reason for the Table
Spool or something that you would like me to check or test, please let
me know.

Jul 20 '05 #1
0 3732

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Krzys! | last post: by
1 post views Thread by david_0 | last post: by
3 posts views Thread by Joachim Klassen | last post: by
1 post views Thread by akaley | last post: by
reply views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.