[Chicago-talk] DBI ODBC PostgreSQL

[Eric Johnson (kablamo)]

I found this.  Perhaps its related?

http://stackoverflow.com/questions/5082789/postgres-query-over-odbc-a-order-of-magnitude-slower#5095399


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

> 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
>>
>
>
> _______________________________________________
> 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/a93b82ab/attachment.html>