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

Odd ASP buffer/cache problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx
"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David