Re: [Haskell-cafe] strange performance issue with takusen 0.8.3
Dear Alistair, At Tue, 7 Apr 2009 11:23:24 +0100, Alistair Bayley wrote: > > 2009/4/6 Marko Schütz : > > > > 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 Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] strange performance issue with takusen 0.8.3
2009/4/6 Marko Schütz : > > 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. > > 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! 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 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. Alistair ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] strange performance issue with takusen 0.8.3
Dear All, 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. 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! During the run of the GHC-compiled executable the cpu load goes up. In top I see %CPU of above 72%. Any ideas that might enlighten are welcome. Best regards, Marko pgpcwfuYc2MfV.pgp Description: PGP signature ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
