Date: Thu, 17 Mar 2005 12:20:16 +0100
To: weberp@paradise.net.nz, pgsql-sql@postgresql.org
Subject: Re: Query performance problem
References: <200503172120.20676.weberp@paradise.net.nz>
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <opsnr4f2i6th1vuj@localhost>
In-Reply-To: <200503172120.20676.weberp@paradise.net.nz>
User-Agent: Opera M2/7.54 (Linux, build 955)


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