exec sp_executesql vs. sp_executesql and performance

This is a odd problem where a bad plan was chosen again and again, but
then not.

Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:

exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @.Parm1)',
N'@.Parm1 int', @.Parm1 = 8609

t2 is a foreign key column, and is indexed.

I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.

I then fiddled with it to see what would result in a good plan.

1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@.Parm1 int',
@.Parm1 = 8609

After doing this, re-executing the original form still gave bad
results.

2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.

After that (surprise!) it also performed well in the original form.

What's going on here?elRoyFlynn (lit@.twcny.rr.com) writes:
> t2 is a foreign key column, and is indexed.
> I took the statement into query analyzer and executed it there. The
> query plan showed that it was doing a scan of the primary key index,
> which is clustered. That's a bad choice.

Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.

> 2) I restored the use of the parm, but removed the 'exec' from the
> start.
> It performed well.
> After that (surprise!) it also performed well in the original form.

Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.

But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.

Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.

But thanks for the response, I'll think about it.|||elRoyFlynn (lit@.twcny.rr.com) writes:
> In this case, the "bad plan" clearly is: a 6-second response time vs.
> a sub-second response when the "best" plan is used.

It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.

> Coincidence , unrelated to the different execution form, that just at
> that moment mss figured out that the other plan was better? I'm
> skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
> had an unintended effect.

It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.

What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, withi
n that stored procedure a SQL statement is built and then executed using the
EXEC command. It looks like that when the generated statement exceeds a ce
rtain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement
start time but no end time. I have also logged every step in the stored pr
ocedure and it just bails on me. Any ideas?Funny you mention that Peter, I run the stored procedure in QA and it runs f
ine. The funny thing is that when the stored proc fails, the rest of the pro
cess completes. I have not been able to get access to the .Net code to view
how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that
stored procedure a SQL statement is built and then executed using the EXEC command.
It looks like that when the generated statement exceeds a certain time threshold,
the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start
time but no end time. I have also logged every step in the stored procedure and it
just bails on me. Any ideas?|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||Andrew, that is what I am thinking too. I have finally gotten a hold of the
code the developer uses to call this. Can any of you see anything wrong wi
th this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initi
al Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in th
e
> stored procedure and it just bails on me. Any ideas?
>
>|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]sql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the stored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?How are you executing the original SP, and have you tried
it in QA ?
>--Original Message--
>I am running into a situation where a program runs a
stored procedure, within that stored procedure a SQL
statement is built and then executed using the EXEC
command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure
exits without any warning.
>I know there is no warning because I have run profiler
and I get a statement start time but no end time. I have
also logged every step in the stored procedure and it just
bails on me. Any ideas?
>.
>|||Your connection timeout setting is probably too low. Try adjusting it.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
--
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
> > Your connection timeout setting is probably too low. Try adjusting it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <Dan@.discussions.microsoft.com> wrote in message
> > news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> > > I am running into a situation where a program runs a stored procedure,
> > within that stored procedure a SQL statement is built and then executed
> > using the EXEC command. It looks like that when the generated statement
> > exceeds a certain time threshold, the stored procedure exits without any
> > warning.
> > >
> > > I know there is no warning because I have run profiler and I get a
> > statement start time but no end time. I have also logged every step in
the
> > stored procedure and it just bails on me. Any ideas?
> >
> >
> >

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
Funny you mention that Peter, I run the stored procedure in QA and it runs fine. The funny thing is that when the stored proc fails, the rest of the process completes. I have not been able to get access to the .Net code to view how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?
|||Andrew, that is what I am thinking too. I have finally gotten a hold of the code the developer uses to call this. Can any of you see anything wrong with this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initial Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in the
> stored procedure and it just bails on me. Any ideas?
>
>
|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]

Excel: terrible performance

Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
a desktop machine. SQL database is about 3GB; spreadsheet comes to about
14MB. Data from 3 tables is being used, related by a field common to all
3; data for one month out of 4 years' total data being extracted.
Performance is extremely slow, 15 minutes or more after selecting Edit
Query on the spreadsheet before the MS Query window comes up, etc.
Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
Query, the windows Task Manager shows workstation CPU usage to be 100%
steady. The server machine is not doing anything else; the Task Manager
shows CPU usage during SQL processing to be moderate.
Is this poor performance to be expected with the amount of data and
spreadsheet size? Is there an interface that will give better
performance than ODBC? Will some other software perform better than
Excel?
Best wishes,
Michael Salem
Hi
Have you thought about using DTS to write the file to a share?
You should also check that you don't have logging enabled on the ODBC
connection.
If you execute the SQL in Query analyser you may be able to improve the
performance using the query plan.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b43a289db966af898968e@.msnews.microsoft.c om...
> Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
> dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
> a desktop machine. SQL database is about 3GB; spreadsheet comes to about
> 14MB. Data from 3 tables is being used, related by a field common to all
> 3; data for one month out of 4 years' total data being extracted.
> Performance is extremely slow, 15 minutes or more after selecting Edit
> Query on the spreadsheet before the MS Query window comes up, etc.
> Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
> still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
> Query, the windows Task Manager shows workstation CPU usage to be 100%
> steady. The server machine is not doing anything else; the Task Manager
> shows CPU usage during SQL processing to be moderate.
> Is this poor performance to be expected with the amount of data and
> spreadsheet size? Is there an interface that will give better
> performance than ODBC? Will some other software perform better than
> Excel?
> Best wishes,
> --
> Michael Salem
|||John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
& 14MB Excel files -- many thanks.

> Have you thought about using DTS to write the file to a share?
> You should also check that you don't have logging enabled on the ODBC
> connection.
> If you execute the SQL in Query analyser you may be able to improve the
> performance using the query plan.
Thanks for these suggestions, I will follow up. I wouldn't expect Query
Analyzer to help, as it is a very simple query, but I will try it.
Reading between the lines it would appear that you're not totally
surprised by the slowness, so it is probably better to seek a more
efficient way of doing the analysis needed than to tweak the present
setup.
I've since learned that a very similar setup on the same hardware but
with a much smaller database works at an acceptable speed.
Best wishes,
michael Salem
|||Hi
Check out DTS as this is a more common way of doing it. See Books online and
http://www.sqldts.com/default.aspx for information regarding how to use
this.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
> John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
> & 14MB Excel files -- many thanks.
>
> Thanks for these suggestions, I will follow up. I wouldn't expect Query
> Analyzer to help, as it is a very simple query, but I will try it.
> Reading between the lines it would appear that you're not totally
> surprised by the slowness, so it is probably better to seek a more
> efficient way of doing the analysis needed than to tweak the present
> setup.
> I've since learned that a very similar setup on the same hardware but
> with a much smaller database works at an acceptable speed.
> Best wishes,
> --
> michael Salem
|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online and
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
>
>
|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online and
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
>
>

Excel: terrible performance

Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
a desktop machine. SQL database is about 3GB; spreadsheet comes to about
14MB. Data from 3 tables is being used, related by a field common to all
3; data for one month out of 4 years' total data being extracted.
Performance is extremely slow, 15 minutes or more after selecting Edit
Query on the spreadsheet before the MS Query window comes up, etc.
Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
Query, the windows Task Manager shows workstation CPU usage to be 100%
steady. The server machine is not doing anything else; the Task Manager
shows CPU usage during SQL processing to be moderate.
Is this poor performance to be expected with the amount of data and
spreadsheet size? Is there an interface that will give better
performance than ODBC? Will some other software perform better than
Excel?
Best wishes,
--
Michael SalemHi
Have you thought about using DTS to write the file to a share?
You should also check that you don't have logging enabled on the ODBC
connection.
If you execute the SQL in Query analyser you may be able to improve the
performance using the query plan.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b43a289db966af898968e@.msnews.microsoft.com...
> Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
> dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
> a desktop machine. SQL database is about 3GB; spreadsheet comes to about
> 14MB. Data from 3 tables is being used, related by a field common to all
> 3; data for one month out of 4 years' total data being extracted.
> Performance is extremely slow, 15 minutes or more after selecting Edit
> Query on the spreadsheet before the MS Query window comes up, etc.
> Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
> still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
> Query, the windows Task Manager shows workstation CPU usage to be 100%
> steady. The server machine is not doing anything else; the Task Manager
> shows CPU usage during SQL processing to be moderate.
> Is this poor performance to be expected with the amount of data and
> spreadsheet size? Is there an interface that will give better
> performance than ODBC? Will some other software perform better than
> Excel?
> Best wishes,
> --
> Michael Salem|||John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
& 14MB Excel files -- many thanks.

> Have you thought about using DTS to write the file to a share?
> You should also check that you don't have logging enabled on the ODBC
> connection.
> If you execute the SQL in Query analyser you may be able to improve the
> performance using the query plan.
Thanks for these suggestions, I will follow up. I wouldn't expect Query
Analyzer to help, as it is a very simple query, but I will try it.
Reading between the lines it would appear that you're not totally
surprised by the slowness, so it is probably better to seek a more
efficient way of doing the analysis needed than to tweak the present
setup.
I've since learned that a very similar setup on the same hardware but
with a much smaller database works at an acceptable speed.
Best wishes,
--
michael Salem|||Hi
Check out DTS as this is a more common way of doing it. See Books online and
http://www.sqldts.com/default.aspx for information regarding how to use
this.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.com...
> John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
> & 14MB Excel files -- many thanks.
>
> Thanks for these suggestions, I will follow up. I wouldn't expect Query
> Analyzer to help, as it is a very simple query, but I will try it.
> Reading between the lines it would appear that you're not totally
> surprised by the slowness, so it is probably better to seek a more
> efficient way of doing the analysis needed than to tweak the present
> setup.
> I've since learned that a very similar setup on the same hardware but
> with a much smaller database works at an acceptable speed.
> Best wishes,
> --
> michael Salem|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
--
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online a
nd
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.com...
>
>

Excel Worksheets Become Corrupt -- Bad Metadata?

One of my main users has had a few Office 2003 SP2 worksheets go corrupt on him and I can't seem to figure out why.

For performance reasons, I recommended that he use page filters whenever possible. As of late, he'll send a workbook along that has a few page filters (between 3 and 4) and one of the page filters gets mixed up somehow.

For example, let's say he has a Product Line page filter and a Product Name page filter. The Product Line page filter has multiple selections enabled and he will go ahead and select a few product lines and save the file so he can simply refresh the data in the future. Eventually, the Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable, usually resulting in a strange error indicating that Excel cannot complete the task with available resources when the drop-down is selected. The other page filters seem to behave normally and the report will still operate until the corrupted page filter(s) are selected.

I've seen 2 of his worksheet/workbooks exhibit this behavior this week. He says he's encountered this before and has rebuilt these reports from scratch only to end up with the same issue.

The AS2005 cube has not undergone any recent changes.

Any thoughts?

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

|||

Tigran.Hayrapetyan wrote:

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

Thanks for the reply Tigran. What are some other details that I can provide?

As for the same names, the values for Product Name and Product Line are vastly different from one another.|||

What I would like to know is what exactly happens in Page filter. When you use the dropdown for Product Name hierarchy, do members from Product Lines hierarchy appear in the Tree control?

But whatever the case, I think the best solution could be to involve Microsoft Support in resolving this issue.

|||

I came accross similar issues and found that the most easiest way to solve this is to use the Excel pivot table wizard to remove all the offending dimensions from the pivot view, refresh the pivot and put back the dimensions in the pivot view.

This will happen mostly when you change dimensions names or when the dimensions member names content change while the client has set a selection on a given member. (no longer in the cube).

Also be carefull with ColumnKeys and ColumNames It lloks like Excel will choke on dimensions where you defined a name and a key column while it was initially only a key column instead of using both a name and a key,

Excel will now use a key (internally, hard to spot). This is mostly impacting Excel views where you use VBA to control the pivot.

Hope it helps a little bit.

So, on the same topic, the issue I am facing now is linked to default member of a time dimension to be the current date.

Initially it works, however, as soon as I update the cube, it will stop to work because the current date set in the Excel view is no longer present in the cube as the current date set as default in the cube. Excel will not pick-up these changes dynamically without removing and putting back-in the dimension. (Excel2003 PTS9)

This lack of dynamic refresh of Excel members is annoying and was already an annoyance in SQL2000.

I would rather have the selector set-back to "ALL" rather than chocking on a missing member.

End users can cope with a selector reset to all, they cannot cope with a cube returning cryptic error messages.

Philippe.

|||

One more comment.

When using VBA to pilot a pivot from code, if you attempt to select a member that is no longer in the cube, you may end-up corrupting your pivot table.

I suspect tha this can also happen in some other cases where you do not use VBA.

Symptoms are as follow:

- Calculated members returns #value error code

- Break-down by individual members are no longer correct while the total remain correct.

Only way out is to rebuild a new pivot table from scratch.

This was happening a lot with Excel XP, it still happen with Excel2003 but very unfrequently.

Just to be on the safe side, when I am done developing a cube and its Excel pivot front-end, I always re-create the pivot from scratch just to make sure it is as clean as it can get.

Philippe