I have a VB app that produces a number of Excel documents from a SQL view. I am using the copyfromrecordset method to transfer my adodb recordset. I am trying to fine tune the system and am trying to find out if the performance I am seeing is normal or not. I have about 56 different queries that go through this process but will just site 2 at the moment. The queries are complex queries on complex views. The first query returns 441 records 20 columns wide and takes about 89 seconds to be transfered into excell using the copyfromrecordset method. The second query returns 791 records 26 columns wide and takes 247 to transfer to excel, only after prompting me about five times that excel is waiting for another application to complete an OLE process. Both queries contain mostly dates and currency values in the columns. The VB application and SQL 2000 server are both running locally on my laptop. The queries are rather complex, basically a pivot table off a view based on about 14 tables. The first one takes about 67 seconds to open, then the additional 89 seconds to be transfered the second query takes about 75 seconds to open before calling the copyfromrecordset. Is the complexity of the queries affecting how long it takes to transfer the opened recordset or is this typical performance for transfering recordsets of these sizes? Any help would be greatly appreciated. Thanks in advance.

Hi eli,

That seems very slow to me. I have transferred thousands of rows and more
than 20 columns before, and the worksheet is typically populated within a
few seconds. Are you sure that CopyFromRecordset is what's slowing it down
(ie, did you time directly before and after the statement)? Have you tried
turning calculation and screen updating off?

How much memory does your laptop have? Running SQL Server 2000 and VB on
the same machine can be slow at times, especially when you're dealing with
very complex queries.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


eli silverman wrote:
I have a VB app that produces a number of Excel documents from a SQL
view. I am using the copyfromrecordset method to transfer my adodb
recordset. I am trying to fine tune the system and am trying to find
out if the performance I am seeing is normal or not. I have about 56
different queries that go through this process but will just site 2
at the moment. The queries are complex queries on complex views. The
first query returns 441 records 20 columns wide and takes about 89
seconds to be transfered into excell using the copyfromrecordset
method. The second query returns 791 records 26 columns wide and
takes 247 to transfer to excel, only after prompting me about five
times that excel is waiting for another application to complete an
OLE process. Both queries contain mostly dates and currency values in
the columns. The VB application and SQL 2000 server are both running
locally on my laptop. The queries are rather complex, basically a
pivot table off a view based on about 14 tables. The first one takes
about 67 seconds to open, then the additional 89 seconds to be
transfered the second query takes about 75 seconds to open before
calling the copyfromrecordset. Is the complexity of the queries
affecting how long it takes to transfer the opened recordset or is
this typical performance for transfering recordsets of these sizes?
Any help would be greatly appreciated. Thanks in advance.

Oh, and you may want to try using a client-side cursor if you're not already
doing so.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


eli silverman wrote:
I have a VB app that produces a number of Excel documents from a SQL
view. I am using the copyfromrecordset method to transfer my adodb
recordset. I am trying to fine tune the system and am trying to find
out if the performance I am seeing is normal or not. I have about 56
different queries that go through this process but will just site 2
at the moment. The queries are complex queries on complex views. The
first query returns 441 records 20 columns wide and takes about 89
seconds to be transfered into excell using the copyfromrecordset
method. The second query returns 791 records 26 columns wide and
takes 247 to transfer to excel, only after prompting me about five
times that excel is waiting for another application to complete an
OLE process. Both queries contain mostly dates and currency values in
the columns. The VB application and SQL 2000 server are both running
locally on my laptop. The queries are rather complex, basically a
pivot table off a view based on about 14 tables. The first one takes
about 67 seconds to open, then the additional 89 seconds to be
transfered the second query takes about 75 seconds to open before
calling the copyfromrecordset. Is the complexity of the queries
affecting how long it takes to transfer the opened recordset or is
this typical performance for transfering recordsets of these sizes?
Any help would be greatly appreciated. Thanks in advance.