most database specific optimization technics wont work, your software will be slow and you will hog quite a lot of resources on the database server if you are unlucky

Although I'd not argue that using the DBD specific to your database is probably the way to go if you only need to access that database what evidence do you have to back up this statement?

Yes and no. For my latest projects with PostgreSQL i measured a speed difference of 2-5% in my evaluation tests.

This page here also claims some speed difference for Oracle.

If (and how much) speed difference you will experience will depend on the amount of data as well as the complexity of the database.

In your orignal post you said:

ODBC is very generic, most database specific optimization technics wont work, your software will be slow and you will hog quite a lot of resources on the database server if you are unlucky. Please don't make your database admin break into tears...

In the orafaq link you provide it says "According to Oracle, their ODBC driver, on average, runs about 3% slower than native Oracle access.". I hardly think 3% slower and your statement using "slow", "hog" and "optimization technics wont work" (sic) matches at all. Please don't spread FUD.

The following code was run on Linux to a remote Oracle 11 database on the same subnet over 100M ethernet using DBD::ODBC and Oracle's 11.2 ODBC driver and DBD::Oracle and the same OCI library the ODBC driver uses (all Perl modules were the most recent).

use strict;
use warnings;
use DBI;
use Benchmark;

my $hodbc = DBI->connect('dbi:ODBC:DSN=OracleODBC-11g;SID=devel','xxx'
+,'xxx');

my $horacle = DBI->connect('dbi:Oracle:host=oracle;sid=devel','xxx','x
+xx');
eval {$hodbc->do(q/drop table benchtest/)};
$hodbc->do(q/create table benchtest (a integer, b varchar(100))/);

timethese(10,
          {'oracleinsert' => sub {insert($horacle)},
           'odbcinsert' => sub { insert($hodbc)}});

timethese(10,
          {'oracleselect' => sub {xselect($horacle)},
           'odbcselect' => sub { xselect($hodbc)}});

sub insert
{
    my $h = shift;

    my $s = $h->prepare(q/insert into benchtest values(?,?)/);

    for (1..5000) {
        $s->execute($_, 'the quick brown fox jumps over the lazy dog')
+;
    }
}

sub xselect
{
    my $h = shift;
   
    my $r = $h->selectall_arrayref(q/select * from benchtest/);
}
[download]

outputs:

Benchmark: timing 10 iterations of odbcinsert, oracleinsert...
odbcinsert: 54 wallclock secs ( 1.46 usr +  0.64 sys =  2.10 CPU) @  4
+.76/s (n=10)
oracleinsert: 54 wallclock secs ( 1.39 usr +  0.68 sys =  2.07 CPU) @ 
+ 4.83/s (n=10)
Benchmark: timing 10 iterations of odbcselect, oracleselect...
odbcselect: 10 wallclock secs ( 4.70 usr +  0.17 sys =  4.87 CPU) @  2
+.05/s (n=10)
oracleselect: 10 wallclock secs ( 5.14 usr +  0.01 sys =  5.15 CPU) @ 
+ 1.94/s (n=10)
[download]

If I use the Easysoft Oracle WP driver it actually works out a little faster than DBD::Oracle and OCI. Of course, you can pick your benchmark but inserting and selecting are most of what people tend to do.

Much reported "slowness" with ODBC is nothing to do with the ODBC API or ODBC drivers at all and is more to do with poorly written applications. As for the so called "database specific optimization technics" (sic) since the Oracle ODBC driver uses the Oracle OCI libraries most of the optimization techniques I am aware of are equally applicable to both.

Never ever try to use DBD::ODBC on a Unify database, where the performance will indeed drop to speeds where granma in here wheelchair will take over with ease. That is because Unify never ever has put time and/or effort into updating their braindead ODBC drivers. They are so 1995.


Enjoy, Have FUN! H.Merijn