Paul Tillotson <[EMAIL PROTECTED]> writes:
> >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?
>
> Just about any query will usually take a few milliseconds (try SELECT 1; to
> see
> the a
Phil,
Just about any query will usually take a few milliseconds (try SELECT 1;
to see the absolute lowest), and so 12 ms is probably about as good as
you can get. For my own part, I consider 50 ms good enough for any
query that is not run inside of a loop. If you want to write suitably
effic
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 ch
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 success
Phil Daintree wrote:
Appreciated you help Paul - many thanks for taking the time.
I view this as merely passing on all the pearls of wisdom I have gleaned
from this list. : )
Advice:
Are you running this inside a transaction? Do so, because if you don't,
then each UPDATE or INSERT or SELECT
Note: If you want to know WHY this takes so long, please tell us how
many times each loop executes and how long each query takes.
Be sure to post an EXPLAIN ANALYZE for each of your queries that you are
running. This will show what plans the planner is using and how long
they are actually taki
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 accou
Phil Daintree wrote:
There are 2 tables used in the sql we need to optimise .
CREATE TABLE chartdetails (
CREATE TABLE gltrans (
So there is a chartdetail record for every period for every general ledger
account. So if there are 5 years x 12 periods (months) and 200 general
ledger accounts
Dear psqlers,
I need your help!
I administer/develop an open source PHP accounting software project (webERP)
that was originally mysql only. Since Christmas I and another member of the
team lower cased all the sql and changed some elements of the SQL to allow it
to use postgres as well. All ap
