Tips for copying really large amounts of data to local tables

[Nanuaraq]

I've a huge amount of data (some 8 million records) in a back-end SQL database, and I am trying to use access for queries.


This works fairly well, in spite of the large amount of records, for simple queries, but once things get complicated the response time drops considerably and error frequency rises.

In another database with similar challenges, I've experienced good results with converting to local tables, or using queries to create new tables with the content locally. This might be a good solution to turn down response time (and then I just need to update table content every once in a while). But the tables n this database are so huge I get error messages when trying to convert to local tables.

Now I'm down to trying to create the local table by setting up table metadata, and then adding the information into the table by making a lot of very small append-queries that gradully fill in the data into the table, but this is error-prone and time consuming.

What tactic would you recommend in order to get a huge amount of data into a local table?




EDIT: Typos and specified where the back-end data is.

[Micron]

An Access table or db has a 2 GB limit, including overhead, so with 8 million recs, you may be there already. Or soon would be. Obviously depends on the number of fields and their data types. Slowness is sometimes due to lack of indexing, but you probably cannot alter the indexes on the source tables. Performing aggregate functions on such large data sets also contributes to slow performance. Having multiple back ends might be your only option if you must import the data.

[Nanuaraq]

An Access table or db has a 2 GB limit, including overhead, so with 8 million recs, you may be there already. Or soon would be. Obviously depends on the number of fields and their data types. Slowness is sometimes due to lack of indexing, but you probably cannot alter the indexes on the source tables. Performing aggregate functions on such large data sets also contributes to slow performance. Having multiple back ends might be your only option if you must import the data.

Other than the added info in thread start, I might add that it seems OK to do a normal query, even a rather complicated one. It's the converting to local table that seems to cause problems.

Doing a query directly on the back-end data takes <1 second, but trying to do a query that creates a new table hangs, and gets me the message that
The database "" cannot be opened

[ArviLaanemets]

Frontend creating new tables as user's task is bad design IMHO. For what you need those? If you want to save some temporary data, then simply create such tables once, and whenever you need to fill them, as first step clear all data (for case somehow some old data remained there), insert new data, and when you don't need those data anymore, clear all data again. And consider having such temporary tables on SQL server too.

I use design like this in SQL Server databases, when some job needs to save results of some calculations for further calculations, as table variables are not indexed, and with big amounts of data calculations using them slow down.

[Nanuaraq]

Frontend creating new tables as user's task is bad design IMHO. For what you need those? If you want to save some temporary data, then simply create such tables once, and whenever you need to fill them, as first step clear all data (for case somehow some old data remained there), insert new data, and when you don't need those data anymore, clear all data again. And consider having such temporary tables on SQL server too.

I use design like this in SQL Server databases, when some job needs to save results of some calculations for further calculations, as table variables are not indexed, and with big amounts of data calculations using them slow down.

As I believe I explained prior, the amount of data makes performance slow. By copying relevant parts of data for evaluation (for example, three years back instead of the whole ten years of data) I can cut down on response time. If I need refreshed data, I refresh the tables (I've a macro for that). Bonus: I do not access production data. You may call this bad design. I call it good design.

[ArviLaanemets]

Anyway, why in Access front-end?

1. Create a SQL view which returns data e.g. for last 3 years like in your example, and link this view like you link tables. NB! You set indexes for linked view too! User gets always fresh data for last 3 years.
2. When the view is anyway too slow for you, create an additional table (with primary key(s), indexes etc.) in SQL Server database, create a stored procedure, which refreshes data in this table (truncates the table and then inserts e.g. into table data from last 3 years. And you create a job, which runs this procedure at some shedule. You also can call this procedure from your front-end and run this procedure at any time the user needs this. And again, you link this table into your front-end.

Using tables you get result faster, but data aren't up-to-date, using views data are up-do-date, but probably to get the result takes somewhat longer time. How much is time difference you have to test yourself. Anyway both solutions work probably faster than the one you try to implement, and are much easier to maintain.