Subject RE: [firebird-support] Replacing views with select procedures
Author Ann W. Harrison
At 02:39 PM 11/1/2004, Alan McDonald wrote:
> >
> > Now that I know how - is there a preferred method for this kind of thing
> > - inline selects versus forselect constructs? Or is it another example
> > of Firebird flexibility and it's just personal preference?

From a performance point of view, first choice is a join. Second choice
is nested FOR SELECT. Third choice is lots of subselects. The query
optimizer can do good things with a join. If you list your tables in
a reasonable order, the FOR SELECT is almost as good. Each subselect
query is executed separately, gaining from lots of cache hits, but still
leaving a long code path to get each field value.

>Joins are by far the most efficient and they should be SQL99 compliant
>rather than the SQL92

Err... you mean SQL-92 rather than SQL-89. And absolutely yes, do use
explicit aliases. Optional aliases are fine for an interactive language.
If you're writing a program, take the time to be explicit about the
source of each column.