[Chicago-talk] DBI ODBC PostgreSQL

[Jay Strauss]

Thanks Eric, unfortunately, on my ODBC DSN (I'm running on windows), the
checkbox for server side prepare is Checked


On Wed, Aug 20, 2014 at 11:11 AM, Eric Johnson (kablamo) <
chicago.pm at iijo.org> wrote:

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