Dan,
You're joining on customer number. An index on customer number on each
of the two tables should be all that's needed.
It does work -- I just want it to work faster. :) There are typically other
where clause conditions, and I have indexes to support them to. But the
indexes are on just the one table. It looks like a MQT with indexes over it
may be a perfect combination, except they i does not yet support Refresh
Immediate. For invoices, Refresh Defered will work just fine as new records
only get inserted in day-end processing. For open orders, the Refresh
Defered may lead to some data latency. But I think I can live with that.
Those orders are more the exception than the norm, so I use my current logic
over just the order / invoice files to find all the ones they get commission
on, and union the MQT restricted to those in territory but commissioned
elsewhere. So even if I can only perform a Refresh Table xxx a few times a
day -- or even once a day -- only those orders will be impacted and
everything else should remain subsecond.
And when Refresh Immediate gets supported, I won't even need that.
I may not even end up using a MQT -- I need more time trials etc -- but it
is a concept I wasn't even familiar with yet so it seems like a good
candidate to experiment and learn more.
midrange.com
