At 11:17 AM 14/03/2003 +0200, you 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?

Generally, when you need only one column from the (otherwise joined) table
AND the embedded select is faster. (Not as facetious as you think it
is!) It can go either way - it's a matter of testing which works best for
the particular vagaries of your data.

A join generally beats multiple subqueries on the same table.

If it's a frequent query (one that could drive people crazy if it's too
slow) make sure you test it both ways with an appropriately-sized set of
test data.

"Embedded select" is OK. You could also call it a subquery, or a
subselect. It's usual to use 'AS' to apply a column name to the output
column, as you do with other expression output.

heLen