ADO & CopyFromRecordset performance

[vali]

Hi there,

Could anybody help?

I am using ADO recordset and Range.CopyFromRecordset method in Exce
VBA application to populate large amount of data into the workseet(s)
I am also using ODBC text driver to open Recordset, Client cursor
original data is in SCV file.

When in debug mode (using Debug.Assert False to break) it performs fas
(I would just hit the Run button). As soon as I remove break point an
let application run alone, it performs terriblly slooow.
For example, 25000 records/lines (3451 KB csv file) are populated in 1
seconds in debug mode vs. 2:16 min in runtime mode ????!!!!

Any advice is welcome!
Thank you.

valentina
:confused

 

[Alex J]

Vali,
I don't have a direct answer - but from playing around with other people's
code using DAO (yes, I know not = ADO), I know that having calculation
manual can make a big difference when actally writing to the sheet. Have you
tried this?

Alex J

 

[keepITcool]

as always:
on excel:
disable events
disable screenupdating
disable calculation
disable AUTOPAGEBREAKS...

on Ado:
client cursor
lock readonly


have you read this page?
it has a nice comparison on ado /excel performance.

http://www.zmey.1977.ru/Access_To_Excel.htm

hth


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Vali,
I don't have a direct answer - but from playing around with other
people's code using DAO (yes, I know not = ADO), I know that having
calculation manual can make a big difference when actally writing to
the sheet. Have you tried this?

Alex J

 

[Jamie Collins]

have you read this page?
it has a nice comparison on ado /excel performance.

http://www.zmey.1977.ru/Access_To_Excel.htm

Just had a glance at it. No discussion of using INSERT INTO..SELECT or
SELECT..INTO SQL syntax. It concludes the fastest approach is using MS
Access's TransferSpreadsheet method; not surprising because this
command does INSERT INTO..SELECT or SELECT..INTO SQL under the hood!
From an Excel perspective, the target workbook would need to closed to
successfully execute this kind of SQL whereas CopyFromRecordset
suggests the target workbook is open.

Jamie.

--

 

[keepITcool]

Done some testing...

Though ofcourse setting up the definite test sets
if always judgemental..


if you're intrested to followup please email...

On purpose: an inefficient sql clause..


I've created 1 xls file..
1 worksheet, 65380 records,5 fields
(ID,data,text,integer,double)
sorted on ID


ado connect:
Const CNNXLS = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"" ;" & _
"Data Source=|FILE|;"

excluded from timings..
clearing activesheet.. etc..
note calculation manual, screenupdating false,autorecorver false,
pagebreaks false,enableevents false


sql command:
Select Top |TOP| * from `sheet1$` as data order by ID"

connect,execute,copy from recordset...

16 00,00000 01,68750
32 00,00000 01,65625
64 00,00000 01,67188
128 00,00000 01,67188
256 00,00000 01,67188
512 00,00781 01,68750
1024 00,01563 01,70313
2048 00,02344 01,73438
4096 00,05469 01,79688
8192 00,10156 01,94531
16384 00,28906 02,28125
32768 00,90625 03,19531
65379 03,37500 06,31250 numrecs<top this takes time

"Select Top |TOP| * into [Excel 8.0;Database=|DEST|].[Sheet1] from
`sheet1$` as data order by ID"

top execute exec+open
16 1,79688 2,41406
32 1,65625 2,24219
64 1,58594 2,17969
128 1,55469 2,15625
256 1,65625 2,25781
512 1,5625 2,17969
1024 1,57813 2,17969
2048 1,65625 2,25
4096 1,71094 2,3125
8192 1,85156 2,45313
16384 1,9375 2,53906
32768 2,47656 3,07813
65536 4,10938 4,70313





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jamie Collins]

keepITcool wrote ...

Done some testing...

excluded from timings..
clearing activesheet.. etc..
note calculation manual, screenupdating false,autorecorver false,
pagebreaks false,enableevents false

Select Top |TOP| * from `sheet1$` as data order by ID"
connect,execute,copy from recordset...

"Select Top |TOP| * into [Excel 8.0;Database=|DEST|].[Sheet1] from
`sheet1$` as data order by ID"

Questions:

1) SELECT..INTO always creates a new defined Name, usually a new
worksheet and a new workbook if it didn't already exist. In order to
achieve a like-for-like comparison, does your CopyFromRecordset
routine also create these objects?

2) Did you try INSERT INTO..SELECT which does not create a new defined
Name etc?

Jamie.

--

 

[keepITcool]

Jamie,

it didn't..
but inserting a name isn't what takes the time.

the timings would probably also be very different
if the source is an (indexed) mdb

use of copyfromrecordset or insert into
would depend on what you need to do,

if you just need to filter out some records and store them
in an unformatted .xls then I'd use insert into.

if you'd need to do some post insert formatting..
i think i'd use copyfromrecordset.. as the time lost
on opening the file outweighs the time gained
in creating the file


just wanted to demonstrate that copyfromrecordset
is NOT the slowboat some people claim it to be.

maybe next week i'll set up some more test
routines.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

keepITcool wrote ...

Done some testing...

excluded from timings..
clearing activesheet.. etc..
note calculation manual, screenupdating false,autorecorver false,
pagebreaks false,enableevents false

Select Top |TOP| * from `sheet1$` as data order by ID"
connect,execute,copy from recordset...

"Select Top |TOP| * into [Excel 8.0;Database=|DEST|].[Sheet1] from
`sheet1$` as data order by ID"

Questions:

1) SELECT..INTO always creates a new defined Name, usually a new
worksheet and a new workbook if it didn't already exist. In order to
achieve a like-for-like comparison, does your CopyFromRecordset
routine also create these objects?

2) Did you try INSERT INTO..SELECT which does not create a new defined
Name etc?

Jamie.

--

 

[Jamie Collins]

keepITcool wrote ...

inserting a name isn't what takes the time.

It must take *some* time (the question is, is it significant?)
therefore to provide a fair comparison both must start from the same
point.

the timings would probably also be very different
if the source is an (indexed) mdb

If both tests were using the same source it would make no difference.

i think i'd use copyfromrecordset.. as the time lost
on opening the file outweighs the time gained
in creating the file

To be honest, in this scenario there will be much difference either
way.

just wanted to demonstrate that copyfromrecordset
is NOT the slowboat some people claim it to be.

That's a worthy thing and I agree it is a very efficient method if
operating on an open workbook.

maybe next week i'll set up some more test
routines.

That would be good. I'll look out for the results.

Jamie.

--