Re: Query performance problem - Mailing list pgsql-general

Note: If you want to know WHY this takes so long, please tell us how
many times each loop executes and how long each query takes.

Be sure to post an EXPLAIN ANALYZE for each of your queries that you are
running.  This will show what plans the planner is using and how long
they are actually taking.

----------------------

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.

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.  In postgres, because of MVCC, this will cause unnecessary table
bloat, as EVERY TIME you update the same row, it will leave behind
another dead tuple that must be reclaimed by VACUUM.  (In other words,
if you update every row row in chartdetails 10 times without VACUUMing,
then this will cause the table to grow 10x.)

As I am still unsure what this code is supposed to do, please tell me:
how many times is each row in chartdetail getting updated?  If it is
getting updated more than once, could you find a way to update it only once?

First, why is this UPDATE statement inside the while loop at all?  It
doesn't look like it references any members of $AccountRow, which is the
variable that governs the WHILE loop that it is within.  Can you take it
out of the while loop and still get the same results?

Second, could you write that update statement to say WHERE period =
$periodno rather than WHERE period >= $period?  If not, why not?

Regards,
Paul Tillotson

Phil Daintree wrote:

>Dear psqlers,
>
>I need your help!
>
>I administer/develop an open source PHP accounting software project (webERP)
>that was originally mysql only. Since Christmas I and another member of the
>team lower cased all the sql and changed some elements of the SQL to allow it
>to use postgres as well. All appears to work beautifully with just a single
>but important exception.....
>
>
>
[snip]

>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.
>
>(The table chartmaster is just a list of general ledger accounts accountcode
>and accountdescription. PK = accountcode)
>
>
>    $ChartAccounts = DB_query('SELECT accountcode FROM chartmaster',$db);
>
>    While ($AccountRow = DB_fetch_array($ChartAccounts)){
>
>        for ($PeriodNo=$CreateFrom;$PeriodNo <= $CreateTo;$PeriodNo++) {
>
>            echo '<LI>' . _('Period Number') . ' ' . $PeriodNo . '</LI>';
>
>            // Check if there is an chart details record set up
>            $sql = 'SELECT count(*) FROM chartdetails
>                    WHERE accountcode='.$AccountRow['accountcode'].'
>                    AND period=' . $PeriodNo;
>            $InsChartDetails = DB_query($sql,$db,'','','',false);
>            $CountRows = DB_fetch_row($InsChartDetails);
>            $AccountExistsAlready = $CountRows[0];
>            DB_free_result($InsChartDetails);
>            if(! $AccountExistsAlready) {
>                $sql = 'INSERT INTO chartdetails (accountcode,
>                            period)
>                    VALUES (' . $AccountRow['accountcode'] . ',
>                        ' . $PeriodNo . ')';
>                $InsChartDetails = DB_query($sql,$db);
>                DB_free_result($InsChartDetails);
>            }
>
>        }
>
>    /*Now run through each of the new chartdetail records created for each
>account and update them with the B/Fwd and B/Fwd budget no updates would be
>required where there were previously no chart details set up ie
>FirstPeriodPostedTo > 0 */
>
>        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);
>        }
>
>    }
>
>
>
>
[snip]

>I am hoping that someone will be able to see an alternative simpler method or
>suggest a method of indexing the pg tables to optmise the required queries. I
>would appreciate any help here men.
>
>Many thanks in advance ....
>
>
>