On 31 Oct 2007, at 14:31, Helen Borrie wrote:

> It's not "just numbers". It's 10 million rows consisting entirely
> of calculated fields, arriving at a sort set for which no indexes
> are available.

I did try moving the presentation layer stuff (the unicode strings)
out of the query and I cancelled the query after 10 minutes. The
MySQL query got about 30% faster doing this.

> The sort isn't on numbers, either, it's on derived text of up to 60
> bytes long, which has to be recalculated repeatedly in the 3-level
> sort. The AVG calculation itself isn't even straightforward,
> thanks to the cast. The sort set is too large to be in memory so
> it's being done on disk in files.

It sounds like this is not implemented as a table scan with
accumulation, but rather as a disk table that is derived from the
original table, sorted and then averaged by taking a pass over each
group in the sorted table. Is that right?

> As to why the MySQL query could be 50X faster, the only clue I can
> see is that, in that query, you are not converting those calculated
> text fields to unicode, i.e., that's two important places where
> there's a lot less work to do. As a reality check, you could run
> the Firebird query without the unicode conversion and see what you
> win.

I'm not sure either and right now I am playing around with a Netezza
appliance, so maybe that is why I was expecting the algorithm I
described above. The only reason I'm comparing to MySQL is that was
where the data was originally stored before I extracted it locally to
Firebird and I was surprised by the change. After all, Windows can
copy the file in a minute or two.

I suppose that maybe the question I need to ask is "What sorts of
tricks can be used to make this sort of accumulation query
efficient?" It was suggested earlier that I needed to make more
temporary table space, but is there a way to get it to just build it
without sorting the whole thing first?

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