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...
>
>