At 11:32 PM 28/07/2005 +0000, Adam wrote:

>Also because it is obviously a join across PKs, left join and join
>will be identical,

Oh no, join and left join are never identical. "JOIN" means the same as
"INNER JOIN". "LEFT JOIN" means the same as "LEFT OUTER JOIN". There is
no such animule as a "LEFT INNER JOIN".

>so using a normal inner join does not tie the optimisers hands to choosing
>a bad plan.

"Using a normal inner join" doesn't "tie the optimiser's hands". The plan
for an inner join and that for an outer join may be different, depending on
what else is going on in the query. What *might* tie the optimiser's hands
is the existence of two identical indexes. The optimiser has been known to
avoid using an index at all, where it might have useful to use one, when it
is faced with two equal indexes.

Take Adam's advice about properly identifying your columns in multi-table
queries, though. Performance isn't the only thing that will be affected by
ambiguous join statements. Usually you will get wrong results, as well. As
Firebird graduates through successive versions, it is going to get harder
and hard to submit ambiguous join statements...I guess you are currently
using Fb 1.0 to get away with the statement you have been using without an
exception being thrown.

Part of your strategy for improving this query ought be preparing the
view's raw query in isql with SET PLAN ONLY turned on (or use a more
sophisticated query tool that displays the plan when the statement is
prepared). Throw some search and order by clauses at the statement, using
the search criteria that you'll use when using the view. Take note of the
plans generated by the optimiser, to see which indexes it uses.

Do the same with the view and compare with the plans for the raw
queries. There will be a good explanation for the poor performance if you
are fore-armed with this knowledge.

./heLen