[Chicago-talk] DBI ODBC PostgreSQL

Jay Strauss me at heyjay.com
Wed Aug 20 07:51:39 PDT 2014


Thanks.  I'll see if I can figure out how to do ->do with multiple inserts

I use copy all the time and its really fast, but in this case I need to use
ODBC


On Wed, Aug 20, 2014 at 9:48 AM, imran javaid <imranjj at gmail.com> wrote:

> 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
>>
>
>
> _______________________________________________
> 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/54e135af/attachment-0001.html>


More information about the Chicago-talk mailing list