I can also do the same thing without sub-queries - I messed about some more since I was keen to ensure backward compatibility with prior versions of mysql that have left/right joins but no subqueries ... quite a bit quicker still!
Query took 0.0037 sec - 1/10th of the sub-query time. SELECT chartmaster.accountcode, periods.periodno FROM chartmaster INNER JOIN periods ON True LEFT JOIN chartdetails ON chartmaster.accountcode = chartdetails.accountcode AND periods.periodno = chartdetails.period WHERE periods.periodno >=1 AND periods.periodno <=63 AND chartdetails.accountcode IS NULL LIMIT 0 , 30 In postgres: SQL executed. Total runtime: 12.241 ms Still this is a third of the time of the sub-query route but 4 times longer than mysql - this must be an install issue? Thanks again for this idea Paul 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
