[Haskell-cafe] [database-devel] Announcing postgresql-libpq-0.8.2.3

[Leon Smith]

I'll have to benchmark withMVar on my system,  but (at least on my old
laptop) a safe foreign function call is also on the order of 100ns.   As
c_PQescapeStringConn and c_PQescapeByteaConn are currently safe calls,
that would limit the maximum time saved at ~50%.

Perhaps it would make sense to make these unsafe calls as well,  but the
justification I used at the time was that the amount of time consumed by
these functions is bounded by the length of the string being escaped,
 which is itself unbounded.    Certainly postgresql-libpq is currently
overly biased towards safe calls;  though when I took the bindings over it
was overly biased towards unsafe calls.   (Though, arguably,  it's worse to
err on the side of making ffi calls unsafe.)

I've also considered integrating calls to c_PQescapeStringConn with
blaze-builder and/or bytestring-builder,  which could help a fair bit,  but
would also introduce dependencies on the internals of these libraries when
currently there is none.

There is certainly a lot of room for optimizing query generation in
postgresql-simple,  this I've been well aware of since the beginning.   And
it probably would be worthwhile to move to protocol-level parameters which
would avoid the need for escaping value parameters altogether,  and open up
the possibility of binary formats as well, which would be a huge
performance improvement for things like numerical values and timestamps.
 Although IIRC,  one downside is that this prevents multiple DML commands
from being issued in a single request,  which would subtly change the
interface postgresql-simple exports.

Best,
Leon


On Mon, Jul 8, 2013 at 10:00 PM, Joey Adams <joeyadams3.14159 at gmail.com>wrote:

> On Mon, Jul 8, 2013 at 9:03 PM, Leon Smith <leon.p.smith at gmail.com> wrote:
>
>> I just fixed a fairly serious performance problem with postgresql-libpq's
>> binding to PQescapeStringConn;   in was exhibiting a non-linear slowdown
>> when more strings are escaped and retained.
>>
>
>  I'd like to point out a somewhat related bottleneck in postgresql-simple
> (but not postgresql-libpq).  Every PQescapeStringConn or PQescapeByteaConn
> call involves a withMVar, which is about 100ns on the threaded RTS on my
> system.  Taking the Connection lock once for the whole buildQuery call
> might be much faster, especially for multi-row inserts and updates.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20130709/6f2a2e22/attachment.htm>