Re: [GENERAL] Query performance problem

[Paul Tillotson]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly