Response Time Problem using Oracle ODBC links to Access Database

Response Time Problem using Oracle ODBC links to Access Database

Posted on behalf of one of our developers:

I am using Access 97 on NT using Oracle's ODBC drivers to link to an Oracle 8.0.5 Database. In Access, when I open the tables directly, they come up almost instantaneously. When I run a query against that table, applying some user defined functions to 'decode' the data, the response time degrades to several minutes. If I run the same query against the same data, using a local table instead of the ODBC link, the response time is once again nearly instantaneous. The source table currently has just over 35,000 records and I am running about a dozen user-defined 'decoding' functions per record. Is it possible that Access/ODBC is making individual calls back to the Oracle database for each user-defined function in the query? Is there a way to get a 'copy' of the data locally to increase my response time (without actually copying the data - which would defeat the purpose of the ODBC link)? Does anybody have a clue?

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)