Re: insert vs select into performance

Mark Lewis wrote:
> If you're performing via JDBC, are you using addBatch/executeBatch, or
> are you directly executing each insert?  If you directly execute each
> insert, then your code will wait for a server round-trip between each
> insert.

I tested both and I found almost no difference in the time it took to
perform it. Mind you this was on a local machine, but I still thought
that it was a bit strange.

> That still won't get you to the speed of select into, but it should
> help.  You could also look at the pgsql-jdbc archives for the JDBC
> driver patches which allow you to use COPY-style bulk loading, which
> should get you to the performance level of COPY, which should be
> reasonably close to the performance of select into.

Yes, someone else on the list suggested this a couple of weeks ago. I
havent had a chance to test it yet, but I am hopeful that I can use it.

The only issue I have is that the test I have done are rather
simplistic, because they are just speed trials. The real system will
probably use 5-10 tables, with up to 100 columns for all tables, that
means I need a stored function which goes through all bulked data and
reinserts them into their real tables. I am worried that this might hurt
the performance so much so that almost the entire bulk copy advantage
diasappears. This is why I am wondering about the details of SELECT INTO
and C functions etc.

regards

thomas