Oracle vs. SQL Server speed -- ODBC

[user-hostile]

Hi. A system at work just migrated the backend from SQL Server (7) to Oracle 10g. The frontend, of course, is Access 2002. There seems to be a real performance hit now when accessing the Oracle data, as compared to the SQL Server data.

The new system is using the Oracle Driver for ODBC, not the Microsoft ODBC for Oracle, as from what I've read, it's faster. Does anyone know of any tips to increase the performance (querying, mainly) of the Oracle ODBC driver? Here's an example:

One report pulls a single record from several related tables. The largest table has about 10,000 records. With the OLD SQL Server application, this report comes up within a second or two. When using Oracle, the same report takes about 20 seconds to display. The network's fine. I haven't tweaked the connection settings for the Oracle ODBC, because I really don't know which settings (if any) will help the Oracle 10g performance. Can someone help, please? Thanks.


U-H

 

[Pat Hartman]

Make sure that all your indexes and primary keys are properly defined. 20 seconds is way too long to return a single record.

 

[Banana]

Quite agree with Pat.

Also, I seem to recall hearing a blurb about Oracle having more than one drivers, some made by a third party company which is preferred for better performance. It wouldn't hurt to sample all different available driver for the given application you're using.

 

[user-hostile]

Make sure that all your indexes and primary keys are properly defined. 20 seconds is way too long to return a single record.

Thanks for the reply. I'll double check the indexes on the Oracle side. But some of the other guys said they ran the queries via the command line (sqlplus) and they returned their record(s) instantly. So that's why we figured there's something slow about the Oracle Driver for ODBC. The query(ies) in question are pretty complex; several tables involved. But yes, I agree, they shouldn't take that long.

Any settings on the ODBC connection I could mess with?


U-H

 

[Banana]

I would recommend that you check into forum more oriented with Oracle- they'll know more about the options available in the driver that you want/don't want enabled and maybe recommend a better driver, if there's one available.

 

[FoFa]

Are using linked tables or a passthough query against oracle?

 

[DJkarl]

I agree with all that it shouldn't take as long as it is...however I would also point out that MS products often work best with other MS products, so you moved from SQL Server which was an MS product to Oracle which is not a MS product.

 

[namliam]

Have the dba run analyses on the tables and indexes, this can really help if that has not been done yet.

 

[user-hostile]

Are using linked tables or a passthough query against oracle?

They're all linked tables. I'm going through the larger queries now, and seeing which ones I can simplify...a bunch of them are queries of queries of queries--ouch! Also having the dbas check the indexes.

Thanks for the responses.


U-H

 

[Banana]

One more thing you could look at: Using OpenDatabase method

 

[FoFa]

Usually I find linked tables give way poorer performance. I usually find I can quicken things up if I do most of the work in a passthrough query, and do the final filtering in Access on a query based off the passthrough. Might not work in your scenario, but thought it worth a mention.