|
ADO & CopyFromRecordset performance
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 -- Message posted from http://www.ExcelForum.com |
ADO & CopyFromRecordset performance
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 "vali " wrote in message ... Hi there, Could anybody help? I am using ADO recordset and Range.CopyFromRecordset method in Excel 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 fast (I would just hit the Run button). As soon as I remove break point and let application run alone, it performs terriblly slooow. For example, 25000 records/lines (3451 KB csv file) are populated in 13 seconds in debug mode vs. 2:16 min in runtime mode ????!!!! Any advice is welcome! Thank you. valentina :confused: --- Message posted from http://www.ExcelForum.com/ |
ADO & CopyFromRecordset performance
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 "Alex J" wrote: 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 "vali " wrote in message ... Hi there, Could anybody help? I am using ADO recordset and Range.CopyFromRecordset method in Excel 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 fast (I would just hit the Run button). As soon as I remove break point and let application run alone, it performs terriblly slooow. For example, 25000 records/lines (3451 KB csv file) are populated in 13 seconds in debug mode vs. 2:16 min in runtime mode ????!!!! Any advice is welcome! Thank you. valentina :confused: --- Message posted from http://www.ExcelForum.com/ |
ADO & CopyFromRecordset performance
keepITcool wrote ...
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. -- |
ADO & CopyFromRecordset performance
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) wrote: keepITcool wrote ... 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. -- |
ADO & CopyFromRecordset performance
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. -- |
ADO & CopyFromRecordset performance
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. -- |
| All times are GMT +1. The time now is 10:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com