From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: Mark Lewis <mark.lewis@mir3.com>
Cc: Thomas Finneid <tfinneid@student.matnat.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert vs select into performance
Date: Tue, 17 Jul 2007 23:10:50 +0200
Message-ID: <469D305A.80606@ifi.uio.no> (raw)
In-Reply-To: <1184705968.387.327.camel@archimedes>
References: <469D1AD3.9030004@ifi.uio.no>
<0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net>
<469D2B8E.7050208@ifi.uio.no>
<1184705968.387.327.camel@archimedes>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tfinneid@student.matnat.uio.no, mark.lewis@mir3.com
Subject: Re: insert vs select into performance
In-Reply-To: <469D305A.80606@ifi.uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox