Hi Ivan / Nico,

Ivan Prenosil wrote:

>
> > I've created a query that is making a SUM of some records. I've checked
> > the PLAN and I noticed that it is using NATURAL sort order. I guess that
> > is normal for aggregate functions ? Is it better to avoid using SUM then ?
> > Performance ?
>
> Interbase needs to visit all records that participate in aggregate function.
> E.g. if you do SUM of values from all records of table, then all records must be
> internally read (->plan NATURAL); it is not possible to read these values from index
> (because of MGA architecture).
>
> On large tables it can really cause a problem with performance.
> Either avoid using SUM, or limit number of rows with WHERE clause,
> or implement some kind of caching summed values, ...

pls correct me if i'm wrong, but in my understanding

a) sum() needs to visit *all* the records to be summed, order doesn't matter
b) in this case, traveling the records in natural order should be the fastest
(avoiding page jumps you'll get trying to walk an index)

so imho plan natural is the best anyway... or do you *have* a where clause that
*could* use an index?? that would change things, of course...

and (Ivan), i can't see how this is affected by the MGA architecture?? the
choice
of an index (or no index) should not influence *what* records are fetched, but
only the order (i do hope so :)

just curious... what have i misunderstood?

regards,
fingerman