Re: [GENERAL] Query performance problem

[Paul Tillotson]

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);
                }
        }
 


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq