MS Access data export performance is horrible

Question

Hello,

I'm using Access 2007. I have a query that accesses a table with a few hundred thousand records in it, which I am joining to another table via an inner join based on two fields. The joining fields are indexed in both tables, and the sort column is also indexed. When I run the query straight up, the result set opens up in about 2 seconds or less. When I try to Export the query results (via right-click, Export, to Text), Access spends (quite literally) hours doing I-don't-know-what before it brings up the export wizard. And then when I make the required changes to the format (making it tab-delimited instead of comma/quote), that's another several hours for the change to take place before I can click the "Start the Export" button, which of course takes several more hours yet. During the hours where it's sitting and thinking, it does keep the CPU pegged at about 50% activity.

Any idea what's going on, or how I can bring this back into the realm of reasonable performance? I did reboot and run it with no other programs open, verified all my indexes, re-checked the query, re-ran it straight up (which again took ~2 seconds), and then immediately re-tried exporting it (which still hasn't presented the wizard screen after ~30 minutes).

table 1: AP_Open
ID  (primary key)
Vend_No (indexed, dups)
Vouch_No
Vouch_date (indexed, dups)
a bunch of other stuff
CompanyCode (indexed, dups)

table 2: Vendors
Vend_No (indexed, dups)
Vend_Name 
a bunch of other stuff
CompanyCode (indexed, dups)

query:
select ap_open.vend_no, ap_open.vouch_no, ap_open.vouch_date, vendors.vend_name (etc--about 40 fields)
From AP_Open INNER JOIN Vendors ON (AP_Open.companyCode = Vendors.CompanyCode) AND (AP_Open.Vend_No = Vendors.Vend_No) 
ORDER BY AP_Open.Vouch_date;

Answer 1

I've sometimes found appending to an existing table or using a make table from a complex query helps. Once you've populated this Table export it, shouldn't take much time at all. (Make sure you have no indexes on the table you are going to populate)

Answer 2

Have you tried using VBA or a macro to export the query? On thing to remember as apenwarr mentioned when you open the query it only returns the first few pages and is working in the background to finish populating the rest but when you export it has to run the whole query first.

Answer 3

There has to be something SO AMAZING wrong here. I can export a joined query between 130,000 invoice details items connected to a products table, and the whole export takes 2 seconds tops (likely less).

You taking about a small data set here, and for a few 100,000 records and a query that rus in less then 2 seconds, then this should only take 10 seconds tops. A few 100 thousand records should not be taking this long.

Is there a network involved? Is this a multi-user application? Have you tried a persistent connection BEFORE running this export (you can fake a persistent connection by opening up any linked table, and minimize it, and then try the export). A table of a few 100,000 records is just so small and tiny, and really should not take more then about 10 seconds. There is some rather MASSIVE detail being left out, such as a network, or something else here. Is this a split database with a link over a network?