Re: Query performance problem - Mailing list pgsql-general

Phil Daintree wrote:

>Appreciated you help Paul - many thanks for taking the time.
>
>
>
I view this as merely passing on all the pearls of wisdom I have gleaned
from this list.  : )

>>Advice:
>>
>>Are you running this inside a transaction?  Do so, because if you don't,
>>then each UPDATE or INSERT or SELECT runs inside its own transaction,
>>and committing each transaction has overhead associated with it.
>>
>>
>
>
>It looks like just putting this code inside a transaction has dramatically
>reduced the problem. Of course I knew this but it needed me to be embarassed
>to actually do it :-)
>
>
>
Glad to hear that this helped.  In case you are interested, the reason
this makes such a dramatic difference is that each transaction's commit
record must be logged to the commit log, which is a sequentially written
file.  (Thus, you can only commit one transaction per revolution of the
disk, and so if you have a 7200 rpm disk, you can't get more than 120
transactions / second on a safely configured system unless your drive
has a battery-backed write cache.)

>>This block of code is INSIDE a while loop that loops once for each row
>>in chartmaster:
>>
>>        for ($PeriodNo=$CreateFrom;$PeriodNo<=$CreateTo; $PeriodNo++) {
>>
>>            $sql = 'SELECT accountcode,
>>                    period,
>>                    actual + bfwd AS cfwd,
>>                    budget + bfwdbudget AS cfwdbudget
>>                FROM chartdetails WHERE period =' . ($PeriodNo  - 1);
>>            $ChartDetailsCFwd = DB_query($sql,$db);
>>
>>            while ($myrow = DB_fetch_array($ChartDetailsCFwd)){
>>
>>                $sql = 'UPDATE chartdetails SET bfwd =' . $myrow['cfwd'] . ',
>>                                bfwdbudget =' . $myrow['cfwdbudget'] . '
>>                        WHERE accountcode = ' . $myrow['accountcode'] . '
>>                        AND period >=' . $PeriodNo;
>>                $UpdChartDetails = DB_query($sql,$db, '', '', '', false);
>>                DB_free_result($UpdChartDetails);
>>            }
>>            DB_free_result($ChartDetailsCFwd);
>>        }
>>
>>
>>It looks like you're updating the same row in chartmaster multiple
>>times.
>>
>>
>
>chartmaster is not being updated ...
>
Sorry--I mean chartdetails.

One tip to remember:  If you have a statement like this:
UPDATE footable SET foocol = 'value' WHERE fooid = 'id';

And it is the case that foocol might already equal value, then write
this instead:
UPDATE footable SET foocol = 'value' WHERE fooid = 'id' AND foocol <>
'value';

This will help because no disk write will actually happen if foocol
happens to already equal value.

>chartdetails is - the chartdetails
>relating to each chartmaster record for all periods >= $PeriodNo I have to
>update all the following periods as the balance b/fwd for all successive
>periods has now increased if we post transaction back in time normally there
>might only be a few chartdetails records for the chartmaster account under
>review with chartdetails records with a period later than the one being
>posted.
>
>
>
Am I correct in thinking that bfwd is basically a running tally of
actual, and bfwdbudget is a running tally of budget, as one might
normally find in a spreadsheet?

If so, you could use this view to calculate the correct value for every
location in the table:

CREATE VIEW newtotals AS SELECT
    thismonth.accountcode,
    thismonth.periodno,
    (SELECT SUM(actual) FROM chartdetails AS q1 WHERE q1.accountcode =
accountcode AND q1.periodno < thismonth.periodno) as cfwd,
    (SELECT SUM(budget) FROM chartdetails AS q1 WHERE q1.accountcode =
accountcode AND q1.periodno < thismonth.periodno) as cfwdbudget,
FROM chartdetails AS thismonth;

And then you could use an update statement:

UPDATE chartdetails
SET bfwd = cfwd, bfwdbudget = cfwdbudget
FROM newtotals
WHERE chartdetails.accountcode = newtotals.accountcode AND
chartdetails.periodno = newtotals.periodno -- JOIN condition
    AND period BETWEEN $CreateTo AND $CreateFrom
    AND (bfwd <> cfwd OR bfwdbudget <> cfwdbudget); -- AVOID needless
updates.

Since I don't have your tables to work with, this might need minor
syntax tweaking, but I'm pretty sure it will work.
I think MySQL doesn't support views yet, but you could replace
"newtotals" in the above update with a big subselect (which I think they
are supposed to support in the latest 4.x version.)

Also: if you don't already have one, that UPDATE statement would
probably use a compound index on (accountcode, periodno).

Now I begin to comprehend why CompiereERP doesn't support MySQL.  ; )

>a row in chartdetails will be updated every time there is a gltrans posting to
>the period and account of the chartdetails ie quite often.
>
>
If it gets updated often it will need vacuuming often as well.

My rule of thumb is that if more than 10% of the data in a table is
getting updated, vacuum immediately before and immediately after the the
code that does all this updating.

Regards,

Paul Tillotson