Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordSet extremely slow
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordSet extremely slow
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" <nospam@invalid je napisao u poruci interesnoj ... Hello, So, we have here a problem of Excel and not of the configuration of databases, servers, odbc, etc., right? regards arno |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordSet extremely slow
....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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Extremely Slow | Excel Discussion (Misc queries) | |||
Opens extremely slow | Excel Discussion (Misc queries) | |||
Extremely slow macro | Excel Programming | |||
EXTREMELY slow toolbars? | Excel Programming |