Subject Re: [ib-support] Embedded SELECT
Author Bojidar Alexandrov
Alexander Tabakov wrote:
> Hi All,
> In a database we have some tables which contain look-up values.
> When performing a query it is much slower to include these tables in
> the join and select the values than to make an embedded select like
> this:
> select
> ...
> (select some_value from tbl_look_up where value_id =
> other_table.value_id) from
> other_table join ....
> where
> ...
> Timing is something like 3s vs 0.6s for one of the queries
> Note:I am not sure if this is called "embedded select" by the way.
> Correct me if needed.
> Any ideas? Generally when is better to perform an embedded select?

See what plan you receive in both cases.
Recalculate selectivity of your indices and if this does not help.
You give FB a tip to force it to select the better plan, or as last resort
you can set the plan staticaly.

IMHO - Generally is beter to use joins - that makes the things more clear
but if your subquery is only one... then is acceptable.

Bojidar Alexanrov
Kodar Ltd.
How To Ask Questions The Smart Way