Tom DalPozzo <t.dalpozzo@gmail.com> writes:
> I've a table in which a field is BYTEA, as I need to store around 200 raw
> bytes in this field.
> I need to perform many many INSERT starting from a common C array and, in
> order to get good performance, I want to do many of them in a single BEGIN
> COMMIT block.
> What is the best choice from libpq?
> PQexec needs to have the bytes encoded into a string. This expansion lowers
> the performance (I tried with \x format, not with the other option yet).
> With PQexecParams, can I send a raw array of bytes as a bytea parameter?
Sure. Specify binary format for that parameter.
> And also, as PQexecParams can't accept multi commands, if I enclose many
> PQexecParams calls between a PQexec("BEGIN;") and PQexec("COMMIT") would
> it work as I wish?
Well, it'll be faster than committing them separately, but have you
considered bundling this up into a single INSERT with multiple VALUES
rows? It'd be a bit tedious to manage by hand, but if the command is
being constructed by a program anyway, it shouldn't be much harder
than separate INSERTs. Much of the time in this is going to go into
parsing and network round-trip overhead, so one statement is going
to handily beat N statements whether they're in a transaction block
or not.
regards, tom lane
