sathiesh81 wrote:

>
> My query is somewhat like this
> ....
> FROM
> (SELECT * FROM TADMINISTRATOR) AS TADMIN,
> (SELECT * FROM AG) AS TAG,
> (SELECT * FROM LATFIN) AS TLATFIN,
> (SELECT * FROM BS1) AS TBS1,
> (SELECT * FROM NETBLOCK) AS TNETBLOCK,
> (SELECT * FROM BS2) AS TBS2

Let me emphasize what Adam already said. Derived tables
(i.e. SELECT FROM (SELECT ...)) are a new V2 feature
intended to allow input streams from aggregated tables.
They are not a better way to do simple joins. The
optimizer may be good enough to turn them into simple
joins in the absence of aggregation, but it may not.
Why take the risk that Firebird will do a full cross
product of the six tables, then apply the restrictions
in the WHERE clause.

>
> WHERE TADMIN.CO_CODE = TAG.CO_CODE AND
> TAG.CO_CODE = TLATFIN.CO_CODE AND
> TADMIN.CO_CODE = TBS1.CO_CODE AND
> TADMIN.CO_CODE = TNETBLOCK.CO_CODE AND
> TNETBLOCK.CO_CODE = TLATFIN.CO_CODE AND
> TBS1.CO_CODE = TLATFIN.CO_CODE AND
> TADMIN.CO_CODE = TBS2.CO_CODE AND
> TBS2.CO_CODE=TLATFIN.CO_CODE AND
> TAG."YEAR" = TLATFIN.LATEST AND
> TBS1."YEAR" = TLATFIN.LATEST AND
> TNETBLOCK."YEAR" = TLATFIN.LATEST AND
> TBS2."YEAR"=TLATFIN.LATEST AND
> TAG."YEAR" = TBS1."YEAR" AND
> TAG."YEAR" = TNETBLOCK."YEAR" AND
> TAG."YEAR" = TBS2."YEAR"

As Adam suggested, all the conditions in this WHERE
clause should be ON clauses in explicit JOINS. However,
the WHERE clause as written brings up some other points.

The Firebird query optimization distributes equalities.
If A = B and B = C, it generates the conjunct A = C.
That eliminates about six lines of that WHERE clause.
When you're using an equality like this, it's often
clearer to write it as


WHERE TADMIN.CO_CODE = TAG.CO_CODE AND
TADMIN.CO_CODE = TLATFIN.CO_CODE AND
TADMIN.CO_CODE = TBS1.CO_CODE AND
TADMIN.CO_CODE = TNETBLOCK.CO_CODE AND
TADMIN.CO_CODE = TBS2.CO_CODE AND
TAG."YEAR" = TLATFIN.LATEST AND
TAG."YEAR" = TBS1."YEAR" AND
TAG."YEAR" = TNETBLOCK."YEAR" AND
TAG."YEAR" = TBS2."YEAR"

Is there a YEAR or LATEST field in TADMINISTRATOR?
If so, you've left it out of the WHERE - not obvious
in the thicket of unnecessary conditions and odd
ordering.


Finally, as Scott Moon points out, if CO_CODE and
"YEAR" or LATEST are the primary key of each table,
you won't get duplicates in this query, so the
DISTINCT is a waste of cycles.

Regards,


Ann