CopyFromRecordSet extremely slow

[arno]

Hello,

Excel 2003

I have been using ADO to query Access Databases (which contain only of
ODBC-links) for ages. Now I try to setup the same configuration (here
Firebird database linked to an Access 2002 mdb) in another company. Running
the queries from Excel 2003 works, however, copyfromrecordset takes 5min to
finish for 2 fields and 1000 records. Does somebody know why this can take
so long and how to speed it up?

The same query in Access works in a second. (The query in the Excel macro is
also very fast, only the line with copyfromrecordset takes ages.) So, we
have here a problem of Excel and not of the configuration of databases,
servers, odbc, etc., right?

regards

arno

 

[sali]

i also found that sometimes request of allocating great number of small
memory pieces may dramaticaly slow down the excel macro execution [as a
allocation goes on, the speed decreases], try to check available memory

just to clear, you may execute the same ado command set from the vbscript
interpreter. vbscript is almost 100% excel vba macro compatible, just test
the problemnatic code segment

 

[arno]

....more Info, maybe a solution:

1) I turned off screenupdating and I have calculation set to manual with not
positive effect.

2) when I import the tables into Access my queries are extremely fast
(compared to the linked tables)

Copyfromrecordset is slow when I use linked tables and fast if I use "real"
tables? Why does copyfromrecordset depend on where I get my data from??


3) So, there appears to be something wrong with the connection, I used this
one:

rs.Open sqlstring.Value, cn, adOpenStatic, adLockOptimistic

and replaced it with this one:

rs.Open sqlstring.Value, cn, adOpenStatic, adLockReadonly

I would also use adOpenForwardOnly, however, then rs.recordscount does not
work anymore and I do not know how many records I received. Maybe there's no
impact on performance.

Now I am within 2 or 3 seconds for my 1000 records

Strange, isn't it?

arno