From: | Paul Tillotson <pntil(at)shentel(dot)net> |
---|---|
To: | weberp(at)paradise(dot)net(dot)nz, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query performance problem |
Date: | 2005-03-19 01:48:35 |
Message-ID: | 423B84F3.4090800@shentel.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-19 04:04:06 | Re: SMP scaling |
Previous Message | Lonni J Friedman | 2005-03-19 00:13:49 | Re: Vaccum analyze. |