Re: Query performance problem - Mailing list pgsql-general

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