Odd SQL Performance problem

I can't post DDL for this, but perhaps some of you may have experience
with this and will know what's happening.
I have the following problem:
INSERT TableName
SELECT * FROM ViewName
ViewName is a view that returns about 500,000 rows. TableName is a
materialized, cached representation of that data because the view is
relatively complex. I'm hesitant to use an Indexed View because I want
control over how and when my materialized copy gets updated.
I stopped the above statement after it executed for nearly 24 hours.
The following set of statements only took a few minutes to complete
successfully:
SELECT * INTO #X FROM ViewName
INSERT TableName
SELECT * FROM #X
Any ideas as to why the second set of statements would execute so much
more quickly than the first? I assume that the first statement is
trying to sort the data as it selects it to do the clustered index
inserts, thus executing multiple times on smaller sets of data, whereas
the second set of statements executes the view, stores the data and
then has a much simpler result set to sort. Are there any types of
hints that I could provide the processor to speed execution of the
first set of statements?
-AlanDid you look to see what the bottleneck was? Was there blocking, were the
disks or cpu pegged or was it just sitting there? Have you checked the
estimated plans for each to see what the differences were?
Andrew J. Kelly SQL MVP
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1145127196.691104.246480@.z34g2000cwc.googlegroups.com...
>I can't post DDL for this, but perhaps some of you may have experience
> with this and will know what's happening.
> I have the following problem:
> INSERT TableName
> SELECT * FROM ViewName
> ViewName is a view that returns about 500,000 rows. TableName is a
> materialized, cached representation of that data because the view is
> relatively complex. I'm hesitant to use an Indexed View because I want
> control over how and when my materialized copy gets updated.
> I stopped the above statement after it executed for nearly 24 hours.
> The following set of statements only took a few minutes to complete
> successfully:
> SELECT * INTO #X FROM ViewName
> INSERT TableName
> SELECT * FROM #X
> Any ideas as to why the second set of statements would execute so much
> more quickly than the first? I assume that the first statement is
> trying to sort the data as it selects it to do the clustered index
> inserts, thus executing multiple times on smaller sets of data, whereas
> the second set of statements executes the view, stores the data and
> then has a much simpler result set to sort. Are there any types of
> hints that I could provide the processor to speed execution of the
> first set of statements?
> -Alan
>|||all of the following options are untested :)
insert into tablename select * from (select * from viewname) as a
or
insert into tablename exec('select * from viewname')
P.S: Regarding the second option... I don't know if this kind of an execute
statement works.. never came across a situation where I had to use this :)|||Check the indexes on the target table.
If the data your inserting has primary key values (or other clustered
index values) that are all over the place (not in relative sequence) or
require insertion somewhere other than the end of the table, you might
run into a lot of page splits.
If that target table is large and the inserted data can't fit into the
indexes free space (fillfactor), it can take forever.
If this is the case, you might be better of dropping the clustered
index...insert the data...recreate the clustered index.
Hope this helps...John|||I think it's more related to the processor trying to batch and insert
blocks of results from the source view. One thing I've noticed is that
when running sp_who2 I seem to catch the spid shifting between SELECT,
INSERT and EXECUTE. I was kindof surprised to see EXECUTE in there.
Yes, it's exactly as you saw it INSERT x SELECT * FROM y -- No triggers
or anything.
My main reason for reporting this is here on USENET that I think it's
something that should be optimized in the query processor/optimizer
within SQL Server.
-Alan
retlaw wrote:
> Check the indexes on the target table.
> If the data your inserting has primary key values (or other clustered
> index values) that are all over the place (not in relative sequence) or
> require insertion somewhere other than the end of the table, you might
> run into a lot of page splits.
> If that target table is large and the inserted data can't fit into the
> indexes free space (fillfactor), it can take forever.
> If this is the case, you might be better of dropping the clustered
> index...insert the data...recreate the clustered index.
> Hope this helps...John|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> insert into tablename exec('select * from viewname')
> P.S: Regarding the second option... I don't know if this kind of an
> execute statement works.. never came across a situation where I had to
> use this :)
Yes, it works, but I'd be surprised if it makes any changes to performance.
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