Re: What's so bad about ODBC

On 11 May 2007 17:11:39 -0700, joel garry <joel-garry_at_home.com> wrote:

>On May 10, 3:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>>
>> It is far better to connect to Oracle using the native connectivity.
>> Get as far away from ODBC as you can.
>>
>> It may not solve your problem but it will still be a good idea.
>
>What exactly might be a problem with ODBC? I ask because a customer
>appears to be moving towards sucking out a lot of stuff into MS
>analytics cubes and such. I think it's using ODBC, but not sure how
>to tell. This is in the listener.log (all that's left after removing
>site-specific identifiers):
>
>CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
>\sqlservr.exe)
>
>At one point, I had shown the customer how to get this stuff (or
>something like it) going with instant client as the driver, but I
>don't know what is happening now (a vendor has recently done an
>install of a beta product, I'm not involved on that end). I don't
>know how different this is than a typical Crystal suck or whatever, it
>does appear to be hitting the db with some frequency at semi-random
>times. Haven't had a chance to see exactly what it is doing yet,
>assume it is just grabbing rows from a few key tables.
>
>So I'm interested in what might be wrong with ODBC from the server
>point of view. If it blows on the other end, that doesn't bother me
>so much. They're talking about maybe turning this into a full-blown
>DSS, which would be fine by me, since that would mean plenty of work
>on the OLTP side (translation: Oracle db upgrade and 3rd party app
>upgrade with perhaps a de-customization, haha), and less worry about
>DSS queries from the clueless on the production system. And maybe I'd
>learn something about the dark side, too.
>
>jg

If you use a function in your where clause, which can't be mapped by the Evil Empire to an Oracle function, the Evil Empire will cause all data to be fetched to the client, and have it filtered on the client.

Also: actual execution paths are often unpredictable, as ODBC may decide to split a join up in two different queries, one being executed on the server, and one executed on the client, and the join being executed on the client.
The probability of this will increase if you combine Oracle and non-Oracle tables.

Long ago, I ended up with converting most queries into passthrough queries, because otherwise the performance was just horrible due to the reasons above.
Mind you: as far as I remember Billy-boy, Stevie-boy and his associates couldn't even map Microsux Jaccess date functions to to_char and to_date.

If you don't have heterogeneous sources, don't use ODBC, if your app supports a native Oracle driver.

-- 
Sybrand Bakker
Senior Oracle DBA