Re: need to speed up query

PFC wrote:
>
>>> What is a "period" ? Is it a month, or something more "custom" ?
>>> Can periods overlap ?
>
>> No periods can never overlap. If the periods did you would be in
>> violation of many tax laws around the world. Plus it you would not
>> know how much money you are making or losing.
>
> I was wondering if you'd be using the same query to compute how
> much was gained every month and every week, which would have
> complicated things.
> But now it's clear.
>
>> To make this really funky you can have a Fiscal Calendar year start
>> June 15 2008 and end on June 14 2009
>
> Don't you just love those guys ? Always trying new tricks to make
> your life more interesting.

Thats been around been around a long time. You can go back a few
hundreds years

>>> Note that here you are scanning the entire table multiple times,
>>> the complexity of this is basically (rows in gltrans)^2 which is
>>> something you'd like to avoid.
>>>
>> For accounting purposes you need to know the Beginning Balances,
>> Debits, Credits, Difference between Debits to Credits and the
>> Ending Balance for each account. We have 133 accounts with
>> presently 12 periods defined so we end up 1596 rows returned for this
>> query.
>
> Alright, I propose a solution which only works when periods don't
> overlap.
> It will scan the entire table, but only once, not many times as
> your current query does.
>
>> So period 1 should have for the most part have Zero for Beginning
>> Balances for most types of Accounts. Period 2 is Beginning Balance
>> is Period 1 Ending Balance, Period 3 is Period 2 ending balance so
>> and so on forever.
>
> Precisely. So, it is not necessary to recompute everything for
> each period.
> Use the previous period's ending balance as the current period's
> starting balance...
>
> There are several ways to do this.
> First, you could use your current query, but only compute the sum
> of what happened during a period, for each period, and store that in a
> temporary table.
> Then, you use a plpgsql function, or you do that in your client,
> you take the rows in chronological order, you sum them as they come,
> and you get your balances. Use a NUMERIC type, not a FLOAT, to avoid
> rounding errors.
>
> The other solution does the same thing but optimizes the first
> step like this :
> INSERT INTO temp_table SELECT period, sum(...) GROUP BY period
>
> To do this you must be able to compute the period from the date
> and not the other way around. You could store a period_id in your
> table, or use a function.
>
> Another much more efficient solution would be to have a summary
> table which keeps the summary data for each period, with beginning
> balance and end balance. This table will only need to be updated when
> someone finds an old receipt in their pocket or something.
>

As i posted earlier the software did do this but it has so many bugs
else where in the code it allows it get out of balance to what really is
happening. I spent a several weeks trying to get this working and find
all the places it went wrong. I gave up and did this query which took
a day write and balance to a point that i turned it over to the
accountant. I redid the front end and i'm off to the races and Fixing
other critical problems.

All i need to do is take Shanun Thomas code and replace the View this
select statement creates

>> This falls under the stupid question and i'm just curious what other
>> people think what makes a query complex?
>
> I have some rather complex queries which postgres burns in a few
> milliseconds.
> You could define complexity as the amount of brain sweat that went
> into writing that query.
> You could also define complexity as O(n) or O(n^2) etc, for
> instance your query (as written) is O(n^2) which is something you
> don't want, I've seen stuff that was O(2^n) or worse, O(n!) in
> software written by drunk students, in this case getting rid of it is
> an emergency...
>

Thanks for your help and ideas i really appreciate it.