--- In firebird-support@yahoogroups.com, Richard Wesley <hawkfish@...>
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;

Richard,

Try and rewrite your where clause using a not exists rather than a not
in. Not in is not fast to use (reason is in the 2.x release notes).
Excel probably handles the null case 'incorrectly'.

For i := 1 to 36630 do
For j := 1 to 42741 do
// this is going to be quite expensive ;)

Is there any reason not to index "Zip_Codes"."Zip". Selectivity on zip
would still be very good I imagine even if there are duplicates. If
you add this index, I imagine performance will improve drastically.

PLAN SORT (JOIN (vw_ExportForTableau NATURAL, zip_codes INDEX(IX_ZIP)))

If you are using Firebird 2 and are doing this sort of thing a lot,
you could create an expression index on the following

EXTRACT(YEAR FROM "vw_ExportForTableau"."Offered_dt")

which would really then make the query fly.

Adam