Fill cache table fast

[snerting]

Hello

Problem:
* A big table in Oracle being queried with one predictable where-clause and then additional clauses depending on the feature turned out too slow to do through ODBC runtime. Working in the forms ended up unusable. The same queries run instantaneous on the Oracle shell, so it's not really about query optimization.

Solution:
* Created a local cache-table in Access which I am filling up _once_ using the predictable where-clause. The content of this table can then be queried with other clauses whenever appropriate. (The predictable where clause is based on values retrieved when starting the application, or when the user changes this in the application - which only happens so often).

This solution worked great until the big Oracle table grew even bigger. It now takes minutes filling up the cache-table.

What I'm wondering is how to fill up such a table in the fastest way possible.

The way I'm building this cache-table now is probably non-optimal. I basically make a Recordset from the Oracle table using the "predictable" where-clause, loop over it and call .AddNew on the cache-table, again looping over Fields to set the values. It sounds slow and it is slow.

I found that myRecordset.GetRows(myRecordset.RecordCount) was really fast, but then I end up with an array. Is there any way to load this array into a table? Without calling .AddNew and setting all Fields one billion times which I assume is the most expensive operation in all of this.


So to wrap it up.

If I have a table in e.g. Oracle - linked through ODBC, what's the fastest way I can make a Recordset from that table be 'imported' to a local Access table?

 

[PHV]

Why not an append (or make table) query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snerting]

Hi, I didn't know this was possible in Access, but it turned out the syntax is just significantly different from Oracle.

I just did a "SELECT * INTO blah FROM (....)", and it's almost exactly 100 times faster.

Problem solved, thanks for pointing me in the right direction, I should have looked more into doing it through SQL myself.