[Chicago-talk] DBI ODBC PostgreSQL

[Jay Strauss]

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;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20140820/940c998b/attachment.html>