On 1 Nov 2007, at 08:39, Alexandre Benson Smith wrote:

>> On 31 Oct 2007, at 11:26, Alexandre Benson Smith wrote:
>>
>>
>>> I don't expect this indices be of any help here, since all data
>>> must be
>>> read (no WHERE clause) is usually faster to read it all in storage
>>> order
>>> and sort them in memory.
>>
>> Since there are only about 350 or so result rows, this seems
>> reasonable.
>
> I don't get you here...

All I meant was that building 350 rows in memory seems efficient.
But it doesn't sound like this is what is going on internally.

>>>> The table has 10,886,400 rows. The dates are all month precision;
>>>> lat/long are on a 2 degree grid and the Anomaly values range from
>>>> -11.7 to 14.09.
>>>>
>>>> Any ideas on why this is 50x slower? It is just numbers!
>>>>
>>>>
>>> Nope, but If I could think in something to try to increase the
>>> performance, I would increase the sort memory space on
>>> firebird.conf,
>>>
>>
>> The number of result rows is relatively small and I would expect that
>> computing averages over such a small set would be IO bound (memory
>> should just be the grouping values, a sum and a count.) Or is there
>> something else going on here that more memory would help?
>>
>
> The same as above, to give you that small dataset (350 rows) all
> the 10
> milion rows should be read and sorted and grouped in memory (or swaped
> out to disk if that does not fill) that is was I suggested you to
> try to
> increase the sort memory space

Sorry, I didn't realise that an accumulation query of this kind would
be implemented in FB by copying the entire unaggregated stream into a
temporary file, sorting it and then aggregating it. I just figured
it would build the aggregated result as it went along. I can try
increasing the sort space, but I think I am looking for a way to make
it do something more efficient as the memory trick does not scale
very well. We do these kinds of queries all the time and a general
approach that avoids the "copy and sort" algorithm would be very good
to know.

TIA,
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html