ODBC JBDC Linked tables

[BobT]

I have an Access 2003 test database that I have written. The tables are
linked to an external Oracle datapase personal edtion running on the same
laptop. The linked tables contained about in total 1/2 million records.

When the tables reside in Access the application performed very well. The
response with linked tables is kinda slow even though the database is on the
same workstation. I am using odbc drivers to link to the tables. I have
eliminated combo boxes, placed indexes where needed.

I still need to speed record retrieval up a bit to make if truely
functional.

Any suggestions? If I can't get the performance to an accceptable level I
will have to investigate another language or tool to say the least...

 

[Joe Fallon]

Access on a local Jet database is blazing fast.
Whenever ODBC and network connections are involved, you give up some speed
in return for other features of DB servers like Oracle and SQL Server. This
assumes the only thing you do is create the back end tables in Oracle, copy
the data over to them and re-link from Access to Oracle. Your app should
work in that situation but it is not optimized to take advantage of the
powerful DB server. Plus, whereas with Jet you can bind to a full table of
data, this is a huge mistake for Oracle. Your forms should always bind to a
single record. You should have search forms or criteria available so the
users can select the record they want to edit. There are also some tricks
for setting the source of a cbo only after the user types in 2 or 3 letters.

How to Handle Combos with Tens of Thousands of Records:

Allen Browne has posted a very useful technique to handle this problem:
http://members.iinet.net.au/~allenbrowne/ser-32.html

Mary Chipman wrote a book on how to optimize Access with SQL Server.
All the same "rules" apply with Oracle.

 

[BobT]

Thank you for the references. I will perform some testing tonight with this
addtional info.

Thanks again