Richard Wesley wrote:

> Hi All -
>
> I have just been given a case where Firebird falls off a cliff
> compared to Excel (using Jet). The culprit seems to be a left join.
>
> SELECT ("vw_ExportForTableau"."Main Type") AS "none:Main Type:nk",
> ("vw_ExportForTableau"."Offered_dt") AS "none:Offered_dt:qk",
> (AVG(CAST("vw_ExportForTableau"."CapRate" AS DOUBLE PRECISION)))
> AS "avg:CapRate:qk"
> FROM "vw_ExportForTableau$" "vw_ExportForTableau"
> LEFT JOIN "zip_codes$" "zip_codes" ON
> ("vw_ExportForTableau"."Zip_cd" = "zip_codes"."Zip")
> WHERE ((NOT (("vw_ExportForTableau"."Main Type") IN ( 'Dev Site',
> 'Mixed')) ) AND ((EXTRACT(YEAR FROM
> "vw_ExportForTableau"."Offered_dt")) = 2006) )
> GROUP BY 1,
> 2;
>

These is really hard to read queries, I would love if you could rewrite
it to easy our reading :-)

> <PLAN>
> PLAN SORT (JOIN (vw_ExportForTableau NATURAL, zip_codes NATURAL))
>

well.. two natural scans, not a surprise it's slow...

I don't know if NOT IN('Value1', 'Value2') could use an index, So could
not help here...

An expression index on EXTRACT(YEAR
FROM"vw_ExportForTableau"."Offered_dt") could help

Or you perhaps could change it to:

"vw_ExportForTableau"."Offered_dt" between '2006-01-01' and '2006-12-31'
So an ordinary index on "vw_ExportForTableau"."Offered_dt" would help.


I had never joined on float fields, but I don't think it's matters since
indices on NUMERIC or DECIMAL fields are in fact stored as floats so any
change to the precision does not invalidates the indices (IRRC), so I
must ask...

Do you have an index on "zip_codes"."Zip" ?

> CREATE INDEX "_tidx_128_31a" ON "vw_ExportForTableau$" ("Offered_dt");
> CREATE INDEX "_tidx_128_22a" ON "vw_ExportForTableau$" ("Main Type");
>
> Any ideas on why this is so much slower?

No indices used neither for the JOIN or the WHERE clause.

> Could it be the float field
> used for the join?

I don't think so, but maybe I am wrong...

I suggest you to double check if there is an active index on the zip table.

> No better in 2.1b2.
>
> 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