Access query very slow when table linked by driver 9.2.xx but fast by 8.1.5
30915Sep 3 2004 — edited Sep 3 2004Hi Please help me with this performance issue which drives me and our Oracle DBA crazy.
We have Windows 2000 and XP machines with Access 2000 linking to tables on Oracle 9i server. We are trying to upgrade Oracle client from 8i to 9i.
The problem is, for certain Access queries, if I pre-link the tables by Oracle ODBC Driver version 8.1.5, everything runs fine and fast (about 5 to 10 seconds). And if I copy this Access frontend application to an 9i client machine with driver 9.2.0.0, it runs fine too.
But, if I pre-link the tables by driver 9.0.0, the query runs very very slow (about 20 minutes) no matter I run the query from an 8i or 9i client machine.
Then I find the "Connect" field in the Access MSysObjects table looks very differently between linking tables by
- driver 8.1.5 (DSN=RCDAWEST_EXEDRA4;UID=QUERYDB;PWD=QUERYDB;)and
- driver 9.2.0 (DSN=RCDAWEST_EXEDRA4;UID=QUERYDB;PWD=querydb;DBQ=EXEDRA4LIB9I;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=T;FWC=F;PFC=10;TLO=0;)
Could somebody help? Thanks!