Ramon,
Thanks for the response. I am using version 5.0.5 - I'm new to Cach�,
although I have a number of years RDMS and OO design experience.
I have tried creating the index on the many side. I've defined the
Relationships/Index as follows:
On the Record class:
Relationship Process As A.Process [ Cardinality = one, Inverse =
Records ];
Index idxRecordProc On Process;
On the Process class:
Relationship Records As A.Record [ Cardinality = many, Inverse =
Process ];
Assuming this is correct, this relationship causes the performance hit even
if I explicitly rebuild the index before running the queries.
With regard to my second point, I've had a look at the Query Plan and the
problem occurs when the index trys to search on a null value.
The query plan is:
- Read master map A.Product.IDKEY, looping on ID.
- For each row:
Read index map A.Record.idxKeyProc, using the given ProcessID and
%SQLUPPER(ProductKey), and looping on ID.
For each row:
Read master map A.Record.IDKEY, using the given idkey value.
Output the row.
The query is only slow when my where clause is something like:
WHERE Record.ProcessID = 249 and Record.ProductKey is null
Strangely, if my where clause is just:
WHERE Record.ProductKey is null
The Query Plan is much more complex and the performance much better.
Also if the Where clause contains anything other than NULL e.g.:
WHERE Record.ProcessID = 249 and Record.ProductKey = "ABC123"
Iit's extremely fast with just the original simple Query Plan.
I'm very confused as this isn't behaving as I would expect.
Thanks again,
Danny
