Re: Query performance problem - Mailing list pgsql-general

See the syntax for INSERT ... SELECT shown here:
http://www.postgresql.org/docs/8.0/static/sql-insert.html

Instead of doing a nested loop to INSERT new records, do it like this:

For ($period = start; $period < end; $period++)
{
    INSERT INTO chartdetails (accountcode, period)
          SELECT accountcode, $period FROM chartdetails WHERE
(accountcode, $period) NOT IN (
              SELECT accountcode, period FROM chardetails WHERE period =
$period
          );
}

Or if you have some table that has 1 row for each period (call it
"periods") then you could simply do:

INSERT INTO chartdetails (accountcode, period)
     SELECT accountcode, period FROM accountcode, period
         WHERE (period BETWEEN $start AND $end) AND (accountcode,
period) NOT IN (
             SELECT accountcode, period FROM chartdetails WHERE period
BETWEEN $start AND $end
          );

Note to others: see the legitimate use of an unconstrained CROSS JOIN?

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

Postgres's SELECT count(*) is slow if many records meet the WHERE clause
being used.  It looks like you're only using testing for 0 or >0 in your
query, so you could use:

SELECT EXISTS (SELECT 1 FROM chartdetails WHERE <where clause goes here>);

This will be much faster since with EXISTS, postgres only runs the query
long enough to find out whether even one row would be returned--if so,
it stops.

Regards,
Paul Tillotson


Phil Daintree wrote:

>Dear psqlers,
>
>I need your help!
>
>I administer/develop an open source PHP accounting software project (webERP) [snip....]
>
>
>    $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);
>        }
>
>    }
>
>
>