First time I ran it it took 5127.243 ms ...... then I did a full vacuum. then ...
SQL executed. Total runtime: 33.707 ms I am keen to just have the one lot of code all in the scripts ... so I was pleased when the identical sql also worked on mysql!!! Your SQL-query has been executed successfully (Query took 0.0350 sec) SQL-query : [Edit] [Explain SQL] [Create PHP Code] SELECT chartmaster.accountcode, periods.periodno FROM chartmaster, periods WHERE ( periods.periodno BETWEEN 1 AND 12 ) AND ( chartmaster.accountcode, periods.periodno ) NOT IN (SELECT accountcode, period FROM chartdetails WHERE period BETWEEN 1 AND 12 ) LIMIT 0 , 30 You'll notice the discrepancy on the timings though! Whilst pg is not performing the way mysql does with innodb - it is at least usable this way. I am guessing there is some gremlin with my install - I'll try an upgrade to v 8. Phil On Fri, 18 Mar 2005 14:07, you wrote: > 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); > > } > > > > } -- Phil Daintree webERP Project Admin ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
