[Chicago-talk] DBI ODBC PostgreSQL
Jay Strauss
me at heyjay.com
Wed Aug 20 07:23:32 PDT 2014
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>
More information about the Chicago-talk
mailing list