Re: Query performance problem - Mailing list pgsql-sql

> 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.
- You should really use 8.0- How much time toes it takes without the INSERT/UPDATES ?- Please post EXPLAIN ANALYZE of
allthe queries- You could do all that with only two queries
 


> (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);
>         }
>
>     }
>
>
> function DB_query ($SQL,
>         &$Conn,
>         $ErrorMessage='',
>         $DebugMessage= '',
>         $Transaction=false,
>         $TrapErrors=true){
>
>     global $debug;
>
>     $result = pg_query($Conn, $SQL);
>     if ($DebugMessage == '') {
>         $DebugMessage = _('The SQL that failed was:');
>     }
>     //if (DB_error_no($Conn) != 0){
>     if ( !$result AND $TrapErrors){
>         prnMsg($ErrorMessage.'<BR>' . DB_error_msg($Conn),'error', _('DB  
> ERROR:'));
>         if ($debug==1){
>             echo '<BR>' . $DebugMessage. "<BR>$SQL<BR>";
>         }
>         if ($Transaction){
>             $SQL = 'rollback';
>             $Result = DB_query($SQL,$Conn);
>             if (DB_error_no($Conn) !=0){
>                 prnMsg('<br />'.  _('Error Rolling Back Transaction!!'), '', _('DB
> DEBUG:') );
>             }
>         }
>         if ($TrapErrors){
>             include('includes/footer.inc');
>             exit;
>         }
>     }
>     return $result;
>
> }
>
>
> 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 ....
>
> --
> Phil Daintree
> webERP Project Admin
>
> -------------------------------------------------------
>