Dear Alistair, At Tue, 7 Apr 2009 11:23:24 +0100, Alistair Bayley wrote: > > 2009/4/6 Marko Schütz <[email protected]>: > > > > 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
pgpjFuF4SFEGd.pgp
Description: PGP signature
_______________________________________________ Haskell-Cafe mailing list [email protected] http://www.haskell.org/mailman/listinfo/haskell-cafe
