Question Oracle - Access ODBC Performance

[purceld2]

I run a Make-table query on three Oracle Enterprise data wherehouse tables via ODBC. I am retrieving about 12500 records which amounts to approx 5.6mb; this takes about 10 mins over an 8mb broadband link.

What is puzzling is when I put more parameter to restrict the rows returned it takes longer which contradicts the Microsoft advice below. I have one field in the query which has 6 = “XXXX” OR criteria, when I run the query I have to end up cancelling it because it takes so long.

Microsoft suggests (See below) linking the tables which has lost me can you created a table in access and link the fields to the tables Oracle database via ODBC and they update automatically?

Use the following tips to improve performance with ODBC data sources:

• Restrict the amount of data that you request from the server. Do not ask for more data than you need. Use queries to select only the fields and rows that you need.
• Use only the functionality that you need. Snapshots are less powerful than dynasets, and they are not updateable. However, snapshots may be faster, particularly for small recordsets without Memo or OLE Object fields.
• Create linked (attached) tables to access server data. Avoid "direct" server access (that is, do not open remote databases and run queries against them). Instead, create attached tables or create pass-through queries.

I would be grateful for any advice to improve performance

Kind Regards

Des

 

[Banana]

The short answer to your question regarding whether its possible to link Oracle tables; yes, it is. It require that you download a Oracle ODBC driver (note there are different drivers by different companies with different performance, research on what is best fit for your need) then in Access you would go to File - External Data - Link Table

And in dialog select ODBC database as file type. You should then be able to select Oracle driver from there.

HTH

 

[Pat Hartman]

Jet makes every effort to send the selection criteria to the server for processing. However, if you use criteria that cannot be directly translated to native SQL because it contains VBA or user-defined functions, you prevent Jet from doing its job. If you have sufficient permissions, you can turn on tools that allow you to actually view the SQL that Access is sending to the server which may give you a clue as to what is causing the problem. Another possibility is to use a pass-through query to select the data and then use a standard Access query that uses the pass-through query as the source for the make-table query.