Richard Wesley wrote:

> Hi All -
>
> I have a scientific data set (Jim Hansen's global temperature anomaly
> grid) which I am trying to do a plot with. When I run a query to
> generate the plot data from an embedded Firebird 2.0.3 database, it
> takes about 25 minutes:
>
> SELECT ((CASE WHEN (F_INTEGERABS("LandOceanTemps"."Lat (deg)") >
> CAST(66.5 AS DOUBLE PRECISION)) THEN _UNICODE_FSS 'Polar'
> WHEN (F_INTEGERABS("LandOceanTemps"."Lat (deg)") <
> CAST(23.5 AS DOUBLE PRECISION)) THEN _UNICODE_FSS 'Tropical'
> ELSE _UNICODE_FSS 'Temperate' END)) AS
> "none:Climate Zone:nk",
> ((CASE WHEN ("LandOceanTemps"."Lat (deg)" >= 0) THEN
> _UNICODE_FSS 'Northern' ELSE _UNICODE_FSS 'Southern' END)) AS
> "none:Hemisphere:nk",
> (EXTRACT(YEAR FROM "LandOceanTemps"."Date")) AS "yr:Date:qk",
> (AVG(CAST("LandOceanTemps"."Anomaly" AS DOUBLE PRECISION))) AS
> "avg:Anomaly:qk"
> FROM "LandOceanTemps"
> GROUP BY 1,
> 2,
> 3
>
> The query plan is.
>
> PLAN SORT ((LandOceanTemps NATURAL))
>

I would not expected anything different, since you have no where clause.

> The same query against a MySQL database containing the original data
> runs in about 47 seconds:
>

MySQL is a complete different engine, even different between each
storage engine used, some of they don't care about transaction
isolation, and the like, once there is a lote less overhead to handle.

> SELECT ((CASE WHEN (ABS(`LandOceanTemps`.`Lat (deg)`) > 66.5)
> THEN 'Polar'
> WHEN (ABS(`LandOceanTemps`.`Lat
> (deg)`) < 23.5) THEN 'Tropical'
> ELSE 'Temperate' END)) AS
> `none:Climate Zone:nk`,
> ((CASE WHEN (`LandOceanTemps`.`Lat (deg)` >= 0) THEN
> 'Northern' ELSE 'Southern' END)) AS `none:Hemisphere:nk`,
> (YEAR(`LandOceanTemps`.`Date`)) AS `yr:Date:qk`,
> (AVG(`LandOceanTemps`.`Anomaly`)) AS `avg:Anomaly:qk`
> FROM `LandOceanTemps`
> GROUP BY 1,
> 2,
> 3
>
> The Firebird schema is
>
> CREATE TABLE "LandOceanTemps" (
> "Anomaly" DOUBLE PRECISION,
> "Date" DATE,
> "Lat (deg)" INTEGER,
> "Long (deg)" INTEGER
> );
>
> CREATE INDEX "_tidx_128_2a" ON "LandOceanTemps"."Date" ASC;
> CREATE INDEX "_tidx_128_3a" ON "LandOceanTemps"."Lat (deg)" ASC;
> CREATE INDEX "_tidx_128_4a" ON "LandOceanTemps"."Long (deg)" ASC;
>

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.

> 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,
and be sure there is no garbage left on the database.

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

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br