Re: psql vs perl prepared inserts

On 4/12/05, Matt Van Mater <matt(dot)vanmater(at)gmail(dot)com> wrote:
> I've been experimenting with loading a large amount of data into a
> fairly simple database using both psql and perl prepared statements.
> Unfortunately I'm seeing no appreciable differences between the two
> methods, where I was under the impression that prepared statements
> should be much faster (in my case, they are slightly slower).

I've been playing with similar issue and in my case the best solution
for bulk insert was using perl to format data in form suitable for COPY
command.

I believe there may be problems if you have RULEs on table you want
to copy data into (IIRC such RULEs will be ignored).

For a test you might want to try also this approach (both from perl and
from psql):

$dbh->do('PREPARE sth_tim (int,inet,boolean,timestamptz) AS INSERT
INTO timestamps VALUES ($1,$2,$3,$4)');
$sth_tim = $dbh->prepare("EXECUTE sth_tim(?,?,?,?)");

...and later execute it. (and likewise with psql). If you'll see gain in speed
with perl it means your DBD::Pg wasn't using server side prepared
statements. (And there is a quite a chance that your psql will outperform
perl using this syntax).

Regards,
Dawid

PS: I have not tested these statements, yet they should be ok; make
sure the argument list (int,inet,boolean...) is correct for your data.