painfully slow export to excel file.
- Thread starter Fuga
- Start date
- Local time
- Today, 13:17
- Joined
- Feb 28, 2001
- Messages
- 30,491
How many records? I know you can't exceed Excel's column limitations, but it isn't quite as hard to exceed the row limits.
Also, are we talking about some sort of threshold event? Like, if you export less than X records, it is fast, but if more than X, it suddenly slows to a crawl?
If so, I wonder if your problem is that you are suddenly "thrasing" your virtual memory. In order to export a query, Access has to build the records of that query AND has to open Excel and a workbook.
Access and Excel both use the "sparse element" paradigm for storage, which means that as they have to build more things, they get bigger. 'cause the storage space for those "things" isn't allocated until the things get built. (The opposite of "sparse" in this context is "pre-allocated.")
So here's poor little Access having to share memory with poor little Excel. And if you believe for even one heartbeat that EITHER of them truly qualifies as "little" then I've got some homes to sell you from south Louisiana. Then, of course, the DB in question has to grow to create the temp records for the result-set of the query. And the spreadsheet has to grow to hold the data transferred from the DB. So here is your system's memory, trying to stuff ten pounds of ... stuff ... in a five pound sack.
If there is a way for you to find and activate the system performance monitor, I would look for things like a sudden growth in page fault rate as you reach the "knee" in a particularly obscure graph of system statistics that demonstrates something called the "parachor." And you reach that knee by making Access and Excel both try to consume LOTS more memory. (You can't show the graph I'm talking about but you CAN usually see page fault rates. That is all you need to see to recognize the symptom.)
You can also use the Start >> Programs >> Accessories >> System Tools >> System Information path to see how much physical memory you have. On smaller machines (say, 1/4 Gb), it is quite easy to swamp physical memory and start swapping to a fare-thee-well. As soon as that happens, kiss your system performance bye-bye. Because of the priorities involved, when Windows swaps, the world stops!
How do you fix this? (1) Buy more memory. (2) Export the query to a Comma-Delimited Text File. Then import the CDTF to Excel. (3) On the odd chance that you have constrained your swap file, see if your disk is nearly full. Also see if your swap file has been given an arbitrarily low size limit. But this might not help since if you are swapping, you are still facing terrible slowdowns. I vote for #2 as the cheapest method of getting the job done.
Also, are we talking about some sort of threshold event? Like, if you export less than X records, it is fast, but if more than X, it suddenly slows to a crawl?
If so, I wonder if your problem is that you are suddenly "thrasing" your virtual memory. In order to export a query, Access has to build the records of that query AND has to open Excel and a workbook.
Access and Excel both use the "sparse element" paradigm for storage, which means that as they have to build more things, they get bigger. 'cause the storage space for those "things" isn't allocated until the things get built. (The opposite of "sparse" in this context is "pre-allocated.")
So here's poor little Access having to share memory with poor little Excel. And if you believe for even one heartbeat that EITHER of them truly qualifies as "little" then I've got some homes to sell you from south Louisiana. Then, of course, the DB in question has to grow to create the temp records for the result-set of the query. And the spreadsheet has to grow to hold the data transferred from the DB. So here is your system's memory, trying to stuff ten pounds of ... stuff ... in a five pound sack.
If there is a way for you to find and activate the system performance monitor, I would look for things like a sudden growth in page fault rate as you reach the "knee" in a particularly obscure graph of system statistics that demonstrates something called the "parachor." And you reach that knee by making Access and Excel both try to consume LOTS more memory. (You can't show the graph I'm talking about but you CAN usually see page fault rates. That is all you need to see to recognize the symptom.)
You can also use the Start >> Programs >> Accessories >> System Tools >> System Information path to see how much physical memory you have. On smaller machines (say, 1/4 Gb), it is quite easy to swamp physical memory and start swapping to a fare-thee-well. As soon as that happens, kiss your system performance bye-bye. Because of the priorities involved, when Windows swaps, the world stops!
How do you fix this? (1) Buy more memory. (2) Export the query to a Comma-Delimited Text File. Then import the CDTF to Excel. (3) On the odd chance that you have constrained your swap file, see if your disk is nearly full. Also see if your swap file has been given an arbitrarily low size limit. But this might not help since if you are swapping, you are still facing terrible slowdowns. I vote for #2 as the cheapest method of getting the job done.
Last edited:
Fuga
Registered User.
- Local time
- Today, 20:17
- Joined
- Feb 28, 2002
- Messages
- 566
Thank you for your reply.
The query extracts about 3000 rows from a table of some 200 000 rows. It has some joins etc, but the query runs sufficiently fast. It is the exporting that takes time. I know this because I did it manually.
Each row has a date field, 3 integer fields and one double field.
This might be the case. I don´t know if the size of the query would change anything, but the first time it happened was after I had imported some data that had previously got lost in the process. However, this might be equivalent to an additional 30000 rows in the table, and no more than a few hundred in the query (I didn´t check this, only an estimate).
I think I´have exported much more than this on occasion.
I didn´t quite understand all this, but I watched the activity handler (?) when doing a manual export and quess what - it went really fast. would this suggest that the problem is with the memory? The inconsistency I mean.
I have a 2.8 Ghz processor and 512 Mb. The swap file was 215 Mb when I looked.
I'm going to try a few automatic exports to see if it returns.
In the meantime, could the norton antivirus program have something to do with it?
Fuga.
The query extracts about 3000 rows from a table of some 200 000 rows. It has some joins etc, but the query runs sufficiently fast. It is the exporting that takes time. I know this because I did it manually.
Each row has a date field, 3 integer fields and one double field.
This might be the case. I don´t know if the size of the query would change anything, but the first time it happened was after I had imported some data that had previously got lost in the process. However, this might be equivalent to an additional 30000 rows in the table, and no more than a few hundred in the query (I didn´t check this, only an estimate).
I think I´have exported much more than this on occasion.
I didn´t quite understand all this, but I watched the activity handler (?) when doing a manual export and quess what - it went really fast. would this suggest that the problem is with the memory? The inconsistency I mean.
I have a 2.8 Ghz processor and 512 Mb. The swap file was 215 Mb when I looked.
I'm going to try a few automatic exports to see if it returns.
In the meantime, could the norton antivirus program have something to do with it?
Fuga.
Last edited:
I was looking for the same answer. When I use a macro in access to export to excel, it takes forever. When I am in the query datasheet view mode and do File > Export to excel, it is very fast.
Any ideas on how to make the macro do the same thing?
Any ideas on how to make the macro do the same thing?
boblarson
Smeghead
- Local time
- Today, 11:17
- Joined
- Jan 12, 2001
- Messages
- 32,059
Not sure about the macro but you might try this code of mine.I was looking for the same answer. When I use a macro in access to export to excel, it takes forever. When I am in the query datasheet view mode and do File > Export to excel, it is very fast.
Any ideas on how to make the macro do the same thing?
Send Table or Query to Excel
Send Form's Recordset To Excel
