Re: Access 97 ODBC to Oracle Question

From my experience Access 97 makes use of Oracle indexes quite efficiently. Example: Retrieved a single record from over 4 million in less than 5 seconds. The database was Oracle 7.3 running on an HP K400 with Unix as the operating system. This also holds true for many other queries I have. One thing that may help is to look at the design -> indexs of you're attached tables to ensure that you're really writing the most efficient query for the table.

Keith

Ben Lin wrote in message ...
>Hi, during the course of developing an application using MS Access 97 as
>front end to retrieve data from a few fairly large Oracle tables (one has
>over 1 million rows). We ran into a performance problem and makes me wonder
>if Access actually takes advantage of Oracle indexes. Since Access does not
>let us change the table structure, we could not add the indexes to these
>linked tables. In all cases, the performance of these queries are slower
>than the equivalent pass-through queries. If we actually download the data
>and create the same Access tables and indexes just like Oracle, the
>performance is still much slower than Oracle which runs on a large Sun
>Enterprise server. Another thing we noticed is that the performance is some
>times even data dependent, i.e. in "Select ....from .,.,.,. where ...
>column = value;", using value 1 the result returned almost right away and
>using value 2 took half a minute or longer. Does Access return the result
>when it has found enough rows to fill in the first page and worry about the
>rest later (when you scroll down). If so, Access must scan down the table
>and it found rows belong to value 1 (which is more popular) much sooner
 than
>it can find rows for value 2. Any help to clarify this mystery will be
>greatly appreciated. Feel free to e-mail me or follow-up to this posting.
>Have a great day!
>
>--
>Regards,
>Ben Lin
>ben.lin_at_unocal.com
>
>
Received on Sat Feb 27 1999 - 00:00:00 CET