There are 2 tables used in the sql we need to optimise .....
CREATE TABLE chartdetails ( CREATE TABLE gltrans (
So there is a chartdetail record for every period for every general ledger account. So if there are 5 years x 12 periods (months) and 200 general ledger accounts this table will be 12,000 records.
There is a gltrans record for every side of a journal entry. This can get to be quite a significant table - easily more than 200,000 per annum - depending on the size of the business obviously.
Can anyone tell me why does the following code chokes ... literally - this works almost invisbly under mysql - pg takes more than an hour even on a very small 30 record database.
There's nothing obvious in the code below. You could probably do it all with one (more complex) sql query (maybe two), but it shouldn't be too bad as it stands.
The code doesn't seem to use gltrans at all though (unless I'm missing something). Is there something missing?
Anyway, the first step will be to get some accurate figures. Can you turn statement logging on in postgresql.conf and also timestamps (unless it's logging to syslogd, in which case you'll get them automatically). That way we can identify exactly where the time is being spent.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
