Re: odbc drive affect performance

"Steve" <smontgomerie_at_hotmail.com> wrote :

> Would the odbc driver cause the optimizer to go to lunch and choose
> different (worse) plans? I tend to think not but others here are
> asking me to explore this possibility.

Yes. ODBC is a Bad Thing (tm) many times. ODBC drivers can be described as "fat drivers" (oppose to the "thin drivers" like JDBC and dbExpress).

For example - unless an option like ODBC_PASSTHRU is enabled, the driver can attempt to perform local processing. This often result in massive overheads as the same checking is performed twice (e.g. the driver syntax checks the SQL before giving it to Oracle to parse and check).

Driver configuration is another issue - certain drivers have silly defaults. Fetch buffer sizes that dates back to the old Pentium 1 days when the average physical RAM on a machine was 24MB or less.

There can be a very drastic performance different on the same platform, with the same application, where application uses ODBC drivers vs Native drivers (e.g. OCI).

The basic answer to your question is a resounding yes IMO. I have seen some severe performance impacts that were directly related to ODBC. Heck, when a driver can go as far as to _change_ your SQL (in an attempt to optimise it), before it passes it to the database engine, server and SQL optimisations that you have done are shot to hell.

--
Billy