[Chicago-talk] DBI ODBC PostgreSQL

[imran javaid]

I would assume ODBC is slower at inserting into Postgres because is it not
native. I would even say that everything in ODBC would be slower.
Do you see a speed up in ODBC if you use do statements instead of
prepare/execute? It would seem from that documentation that it may be
faster. Also, try sending chunks of insert statements within one do call,
see if that speeds things up a bit.
I presume you already know that with DBD:Pg, pg_putcopydata is the fastest
way.

-imran


On Wed, Aug 20, 2014 at 9:23 AM, Jay Strauss <me at heyjay.com> wrote:

> Hi, does anyone have experience or ideas of why it takes so long to insert
> records into PostgreSQL using DBD::ODBC?
>
> I've been googling but haven't found anything.
>
> Below is my script.  But in a nutshell, I am trying to insert 100,000
> records into PostgreSQL.  In my script I can toggle between using the
> native DBD::Pg or DBD::ODBC
>
> My insert rates are:
>
> DBD::Pg = 4000 rec / sec
> DBD::ODBC = 10 rec / sec
>
> In my script I had to use $dbh->{odbc_exec_direct} = 1;
>
> Otherwise I'd get an error like:
> >perl odbc.pl test new.dat
> Wed Aug 20 09:11:44 2014
> DBD::ODBC::st execute failed: No query has been executed with that handle
> (SQL-HY000) at odbc.pl line 46, <$fh> line 1.
>
> Any ideas of how to speed up ODBC performance?
>
> Script below:
>
> Thanks
> Jay
>
>
> use warnings;
> use diagnostics;
> use strict;
>
> use DBI;
>
> my ($table_name, $data_file, $delim) = @ARGV;
>
> my $dbname = "demo";
> my $host = "192.168.28.122";
> my $port = 5432;
> my $username = "hl7";
> my $password = "";
>
> my $ODBC='dbi:ODBC:DSN=JustOneDB32';
> my $PG="dbi:Pg:dbname=demo;host=$host;port=5432";
>
> my $DATABASE=$ODBC;
> #my $DATABASE=$PG;
>
> $delim = $delim || '|';
>
> my $dbh = DBI->connect($DATABASE,
>                       $username,
>                       $password,
>                       {AutoCommit => 0, RaiseError => 1, PrintError => 1}
>                      )
> or die $DBI::errstr;
>
> $dbh->{odbc_exec_direct} = 1;
> my $sth = $dbh->prepare('insert into test (col1,col2,col3,col4) values
> (?,?,?,?)');
>
> print scalar localtime,"\n";
>
> open(my $fh, "<", $data_file);
> while (<$fh>) {
> chomp;
> my @fields = split(/\|/);
>
> #print "inserting: ", join(",", @fields), "\n";
> $sth->execute(@fields);
>
> }
>
> print scalar localtime,"\n";
>
>
> $dbh->commit;
> $dbh->disconnect;
>
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20140820/0177e47c/attachment.html>