Hi everybody,

We're using extensively lookup fields in TTables and TQuery components
and recently found that if an FK is NULL the BDE still goes out trying
to look it up.

The real problem is that it's done using FULL scan of a lookup table. If
this lookup table happened to be large the application suffers major
performance hit.

Short of converting all of the lookup fields that may be NULL to be
calculated and perform search manually - any other ideas would be
greatly appreciated

(FYI we are on Oracle 7.3.4)

Thanks in advance,
Arik

Arik,

Your question doesn't say clearly what is Null and what is being
looked up. If you know the Null value before the lookup, you may be
able to do something like:
        if SomeQuery.FieldByName('somecol').Value <> Null then
                {do the lookup}

If you don't know the Null ahead of time then you may be out of luck.
To the database, Null is just another value. Unless you intervene, it
has no way of knowing about Null until the lookup is done.

Also, any lookup in a relational database should be done on an index,
especially on a long table. Performance is managed primarily by table
design and indexes.

HTH

Phil Cain

--

Break the master-detail connection and manually trigger the detail (lookup)
from the master's OnScroll event.

Or

Use a TQuery instead of a TTable and get the lookup data using a left join.

Depends on exactly what you're doing to tell which of the above (or
something else again) would be best, but at the very least you should toss
all TTables and use TQuerys.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Bill of NO Rights; ARTICLE VIII:  You DON'T have the right to demand that
our children risk their lives in foreign wars to soothe your aching
conscience.