--- In firebird-support@yahoogroups.com, Alexandre Benson Smith

> your plan
>
> PLAN
> SORT (
> JOIN (R NATURAL,
> F INDEX (IDX_CP_FDA_MAP_NDC9),
> B INDEX (IDX_CPNUM_CPNUM),
> L INDEX (IDX_NDCLISTINGS_NDC9)
> )
> )
>
> says:
> Read table R from the first record to the end in the storage order,

then:

> read the record from table F using index IDX_CP_FDA_MAP_NDC9 to match
> the clause (F.NDC9 = R.NDC9), then
> read the record from table B using index IDX_CPNUM_CPNUM to match the
> clause (B.CPNUM = F.CPNUM), then
> read the record from table L using index IDX_NDCLISTINGS_NDC9 to match
> the clause (L.NDC9 = R.NDC9), then
> filter out the results where L.TRADENAME LIKE '%'||UPPER('aceta')||'%'
> and then
> sort out the result set in memory to remove all the duplicates
>
>
>
> see you !

Alexandre - Thanks for the advice. I've followed your advice and
separated Links from Filters, using ANSI SQL Join syntax. I've also
created Expression Indexes for the table columns that the data in NDC9
comes from. I've changed my use of UPPER(...) LIKE '%'||UPPER(...)...
to CONTAINING (case insensitive search) which can't be indexed but
seems to be a little bit faster.

Sometimes it seems like these are the kinds of issues that the
optimizer should be sorting out, kind of like SQL Server does... guess
I just got spoiled.

In testing following your advice I also tested the WHERE clause to use
LIKE :var1||'%', but it didn't cause the optimizer to choose the index.

The performance as it is isn't bad - sub-second selects in my
procedures - but the concern is with large enterprise usage
performance. I try to do everything I can before rolling things out.

Thanks again for your excellent advice.