On Mon, 22 Mar 2010 13:38:05 -0700, QB wrote:

Correct me if I misunderstood, but I read that one should index fields that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB

Indexing the underlying fields in a calculated expression will not help, if
you're applying criteria to PercentDiff: it will still have to retrieve all
the rows, do the calculation on all of them, and only then apply the
criterion.

If you find performance is unacceptable (try it first, you might be pleasantly
surprised), post back with the actual criterion you're using; it may be that
some alternative way of framing the question will allow you to search for all
NewValues based on some expression on OldValue. Failing that, this might be
one of the rare instances where it's appropriate to store a derived value.
--

John W. Vinson [MVP]