[Haskell-cafe] strange performance issue with takusen 0.8.3

[Marko Schütz]

Dear Alistair,

At Tue, 7 Apr 2009 11:23:24 +0100,
Alistair Bayley wrote:
> 
> 2009/4/6 Marko Schütz <MarkoSchuetz at web.de>:
> >
> > I have an application where some simple data extracted from some
> > source files is inserted into a PostgreSQL database. The application
> > uses Takusen and is compiled with GHC 6.8.3. Some (59 in the test
> > data) of the selects take on average 460ms each for a total time for
> > this sample run of 30s. I prepare one select statement at the
> > beginning of the run into which I then bind the specific values for
> > every one of the selects. It does not seem to make a difference
> > whether I do this or whether I just use a new statement for every
> > select.
> >
> Hello Marko,
> 
> I'm finding it hard to see what the problem is here. Is it that your
> query takes 460ms, and you need it to be quicker? Or is it something

Yes, the problem was (see below) that I will end up running this
program thousands of times and 30s per run is way too long.

> else? It would help to have some example code. Can you make a test
> case which reproduces te problem, that you could share?
> 
> > For comparison, I have collected the SQL statements in a file with
> > PREPARE ... and DEALLOCATE for _every_ select and then run this file
> > through psql. This takes 2s!
> 
> If all you are doing is preparing and deallocating - i.e. not
> executing - then that will be very quick, because the queries are
> never executed.

I should have mentioned that I do indeed have "an EXECUTE ... in
between PREPARE ... and DEALLOCATE".

In the meantime I found out what the issue was:

I was using `Int` on the Haskell side which was translated to
`NUMERIC`, but the relevant columns of the database have type
`INT`. Consequently, the indices were not being used!

To debug this I used an explicit EXPLAIN ANALYZE ... on the name of
the prepared statement from within a withPreparedStatement.

Best regards,

Marko
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
Url : http://www.haskell.org/pipermail/haskell-cafe/attachments/20090407/fff8d5d7/attachment-0001.bin