CODEWITHSUNDEEP
vbacsvms-accessms-access-2016
Kashif
Kashif

Reputation: 4632

MS-Access: Slow queries on linked CSV files

My Access 2016 db has links to several CSV files, some of them quite big (>120MB). When running complex queries on these CSV files, the speed is too slow to be practical. I cannot make local tables of these linked CSV files because after every cycle of running queries, these CSV files are refreshed with updated data by an external server.

What can I do to speed things up significantly?

Upvotes: 1

Views: 755

Answers (2)

Gustav
Gustav

Reputation: 55841

Another option is to load (not exactly the same as import) the CSV files into MySQL:

Import CSV File Into MySQL Table

This is a very fast process - close to a simple file copy.

Then use MyODBC to connect to the MySQL instance via ODBC.

Upvotes: 1

ThunderFrame
ThunderFrame

Reputation: 9461

You would be wise to import the CSV data to local temporary/staging tables. You can then run queries against the temporary table, and if the query needs to use joins, or is very complex, you can consider using indexes on the temporary table as appropriate.

Once you're done with querying the data, you can truncate or drop the temporary tables.

Upvotes: 2

Related Questions