Microsoft Access Make-Table Performance with Pass-Through Query

Question

Question for you Access guru's out there:

I have an Oracle pass-through query which returns a result set in about 2 seconds in the Access program. However, when I try to use that pass-through query inside a make-table query, it drags for about 7 minutes. (The file currently resides on my desktop and all tables are local (not linked)).

In an effort to improve performance, I created delete/append queries and ran it with VBA. Then I tried DAO db.Execute with explicit string SQL queries, referencing the pass-through query (INSERT/SELECT). All methods are extremely SLOW! I would love to get it close to the actual run time of the pass-through (again, 2 seconds).

Any insight?

Answer 1

Likely, there is no actual, large performance difference between make-table and pass-through query. The query actually takes minutes to run while you can view some results in seconds. Scrolling down the query window may show query still being rendered.

Because pass-through queries interface with an ODBC driver/OLEDB provider to parse SQL statements between client (i.e., MS Access) and database (i.e., Oracle) across a network, several environment or software reasons can be the factor.

1. Network traffic between client machine and server. Avoid WAN for hard-wired LAN connections. Never use internet folders or run Access on flash drives or drives of limited disk space.
2. ODBC driver/OLEDB provider that may be outmoded. Try finding the most recent version compatible for your Oracle version and MS Office version. And try running on 64-bit architectures.
3. Complex or large Oracle data types that cannot map to JET/ACE data types such as high precision number types or large binary object types. See this Oracle doc.
4. Bloated Access app that should be compacted and even decompiled.
5. Database without split architecture where application object (queries, forms, reports, macros, modules) are divorced from tables.