Performance issue with export to Excel (1 Viewer)

[boerbende]

Dear readers,

I have a query generating from 100 to 80.000 records, even more
The problem is that the performance is low when trying to export the data to Excel, even if the number of records is low

I have tried several test, but I do not see a big difference in performance.
Is there anybody who knows a better solution

-----------
Test 1
Set qdf = CurrentDb.QueryDefs(“qry_test”)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdf.Name, "O:\test.xlsx"

-----------
Test 2
DoCmd.OutputTo acOutputQuery, “qry_test”, acFormatXLS, _
"O:\test.xlsx", True

-----------
Test 3
Set objExcelApp = CreateObject("Excel.Application")
Set objworkbook = objExcelApp.Workbooks.Open("o:\2template.xlsx")

objExcelApp.Application.Visible = True
'Set objExcelWrkBk1 = objExcelApp.Workbooks.Add

Set rst = Me.frm_SubForm.Form.Recordset
Set rng = objworkbook.Worksheets("Data").Range("A2")
rng.CopyFromRecordset rst

-----------
Test 4
When executing the recordset to the subform, you can export this via the menu, external data, export to excel,

-----------
Many thanks

Ben

 

[JHB]

...
The problem is that the performance is low when ...

How low is low, (1 minute, ½ hour or ...)??
What about the speed if you export it to a local drive or where the database is located?

 

[The_Doc_Man]

I have no feel for the difference between tests 1 & 2, but I would guess test 3 would be slower because it gets relayed through extra layers.

My question is whether you have a lot of conversions to be made during the output. Data type conversions are not uncommon with Access/Excel interfaces.

I would be curious to know a couple of things about your setup and your environment.

First, how much free space do you have on your disk, and has it been defragmented recently?

Second, how many other things are in memory at the time, and how big is your page/swap file?

Third, can you give us some timing values for your tests that are apples-to-apples comparisons? Like, if you converted 1000 records by each of the four methods, how long did each one take? Since you have VBA there, you can capture timestamps before and after each case and figure the number of seconds. Or, you can do the Timer function that gives you msec since midnight for each timestamp.

Fourth, is there an ORDER BY clause in the query used to provide the output recordset?

 

[boerbende]

Hello,

sorry for the late response.
Indeed is 3 the slowest. I am talking about maybe 1-several minutes to export data from Access to Excel. So it is possible to wait for that, but when I copy paste the same data from Excel to Excel via clipboard, even with 80.000 records this is nearly instantaneous.
Besides, even 700 records take a lot of time. I just don't know why.

I have enough free disk.
Don't know about page / swap file. but this happens not only on my computer, also on others
There is no order, no..

I just want to dump very quickly from Access to Excel. This to analyze the data in different ways and after wards generate a new set

Thanks

Ben

 

[GohDiamond]

What version of Access are you using and,
When was the last time you did "Compact and Repair" on your dB?

Are you running the database from your C:\Drive (local Drive) or over a LAN with the DB on the LAN?

Cheers!
Goh

 

[The_Doc_Man]

when I copy paste the same data from Excel to Excel via clipboard, even with 80.000 records this is nearly instantaneous.

This fact alone screams "format conversion" issues. Cut/Paste from Excel Spreadsheet to (another) Excel Spreadsheet does NO conversions. Unfortunately, outbound from Access is harder to control than inbound when it comes to formatting issues.

When you do this putative export, do you do so to an extant spreadsheet or do you create a new one?

In the former case, the columns potentially have pre-defined formatting, which affects what Access might have to do during the export. In this case, the "fix" would be to export a query that already conforms to the desired formatting.

In the latter case, raw formatting should not be the issue, since in this case Excel has nothing to "get in the way" of using the literal formats you provide from the export query.

 

[gemma-the-husky]

what about using transfertext, and saving a csv

then import the csv to excel.

 

[The_Doc_Man]

Dave, that method works for me frequently, but in the first post, boerbende says he is working with up to 80,000 records. It occurs to me that depending on the version of Excel AND the exact spreadsheet type, you might run into a hard limit of 65535 rows. For the extreme case, will a .CSV format allow that?

 

[gemma-the-husky]

A csv is just a text file, with no limit in size.

if it has more rows than excel will allow, you will hit the problem you mention.

I just thought using a csv might remove any conversion speed issues.