<div dir="ltr">Thanks. I'll see if I can figure out how to do ->do with multiple inserts<div><br></div><div>I use copy all the time and its really fast, but in this case I need to use ODBC</div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Wed, Aug 20, 2014 at 9:48 AM, imran javaid <span dir="ltr"><<a href="mailto:imranjj@gmail.com" target="_blank">imranjj@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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.<div>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.</div>
<div>I presume you already know that with DBD:Pg, pg_putcopydata is the fastest way.</div><div><br></div><div>-imran</div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div class="h5">On Wed, Aug 20, 2014 at 9:23 AM, Jay Strauss <span dir="ltr"><<a href="mailto:me@heyjay.com" target="_blank">me@heyjay.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><div dir="ltr">Hi, does anyone have experience or ideas of why it takes so long to insert records into PostgreSQL using DBD::ODBC?<div>
<br></div><div>I've been googling but haven't found anything.</div><div><br></div>
<div>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</div><div><br></div><div>My insert rates are:</div>
<div><br></div><div>DBD::Pg = 4000 rec / sec<br></div><div>DBD::ODBC = 10 rec / sec</div><div><br></div><div>In my script I had to use $dbh->{odbc_exec_direct} = 1; </div><div><br></div><div>Otherwise I'd get an error like:</div>
<div><div>>perl <a href="http://odbc.pl" target="_blank">odbc.pl</a> test new.dat</div><div>Wed Aug 20 09:11:44 2014</div><div>DBD::ODBC::st execute failed: No query has been executed with that handle (SQL-HY000) at <a href="http://odbc.pl" target="_blank">odbc.pl</a> line 46, <$fh> line 1.</div>
</div><div><br></div><div>Any ideas of how to speed up ODBC performance?</div><div><br></div><div>Script below:</div><div><br></div><div>Thanks</div><div>Jay</div><div><br></div><div><br></div><div><div>use warnings;</div>
<div>use diagnostics;</div><div>use strict;</div><div><br></div><div>use DBI;</div><div><br></div><div>my ($table_name, $data_file, $delim) = @ARGV;</div><div><br></div><div>my $dbname = "demo"; </div><div>my $host = "192.168.28.122"; </div>
<div>my $port = 5432;</div><div>my $username = "hl7";</div><div>my $password = "";</div><div><br></div><div>my $ODBC='dbi:ODBC:DSN=JustOneDB32';</div><div>my $PG="dbi:Pg:dbname=demo;host=$host;port=5432";<br>
</div><div><br></div><div>my $DATABASE=$ODBC;</div><div>#my $DATABASE=$PG;</div><div><br></div><div>$delim = $delim || '|';</div><div><br></div><div>my $dbh = DBI->connect($DATABASE,</div><div> $username,</div>
<div> $password,</div><div> {AutoCommit => 0, RaiseError => 1, PrintError => 1}</div><div> )</div><div><span style="white-space:pre-wrap"> </span>or die $DBI::errstr;</div>
<div><br></div><div>$dbh->{odbc_exec_direct} = 1;</div><div>my $sth = $dbh->prepare('insert into test (col1,col2,col3,col4) values (?,?,?,?)');</div><div><br></div><div>print scalar localtime,"\n";<br>
</div><div><br></div><div>open(my $fh, "<", $data_file);</div><div>while (<$fh>) {</div><div><span style="white-space:pre-wrap"> </span>chomp;</div><div><span style="white-space:pre-wrap"> </span>my @fields = split(/\|/);</div>
<div><br></div><div><span style="white-space:pre-wrap"> </span>#print "inserting: ", join(",", @fields), "\n";</div><div><span style="white-space:pre-wrap"> </span>$sth->execute(@fields);</div>
<div><br></div><div>}</div><div><br></div><div>print scalar localtime,"\n";</div><div><br></div><div><br></div><div>$dbh->commit;</div><div>$dbh->disconnect;</div></div></div>
<br></div></div>_______________________________________________<br>
Chicago-talk mailing list<br>
<a href="mailto:Chicago-talk@pm.org" target="_blank">Chicago-talk@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
Chicago-talk mailing list<br>
<a href="mailto:Chicago-talk@pm.org">Chicago-talk@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br></blockquote></div><br></div>