Re: [SQL] Query performance problem

PFC Thu, 17 Mar 2005 03:24:31 -0800

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.' ' . DB_error_msg($Conn),'error', _('DB ERROR:')); if ($debug==1){ echo ' ' . $DebugMessage. " $SQL "; } if ($Transaction){ $SQL = 'rollback'; $Result = DB_query($SQL,$Conn); if (DB_error_no($Conn) !=0){ prnMsg(' '. _('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

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: [SQL] Query performance problem

Reply via email to