Re: Query performance problem

> 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 all the 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
>
> -------------------------------------------------------
>