At 12:06 PM 2/14/2001 +0100, Jörg Schiemann wrote:

>Is a query with a subquery faster then a joined query if I want all fields
>from the [Sample]Detail and only OrderNo from [Sample]Master?

That's a hard question. The fastest query is the one that
examines and rejects the fewest rows. In this case, the
selection criteria are very important. If you ask for the
full result, the fastest way to get it is certainly a join.
InterBase will walk the master table, looking up the details
for each master. (The inverse is the same number of rows,
but more index lookups.)

If you've got criteria (other than the join condition) that
reference the master table, then a join is the right way
to go. InterBase will use the index on the master table
(assuming that the condition references an indexed field).

If your criteria (other than the join condition) reference
the detail table, then you may be better off with a sub-query.
InterBase will lookup the detail records with an index, then
get the master by primary key. It should do that with a join
as well, but sometimes it goes peculiar. The sub-query limits
its choices and leads it down the path of righteousness.

As for the question of sorting, here it gets pretty complex.
Guido's right - in some cases a sub-query might be faster
to the first result row (perhaps not to the last) but that
depends on what you're sorting by and which table becomes
the sub-query.


Regards,

Ann
www.ibphoenix.com
We have answers.