As I see it, one of the worst aspects of that query is that for each record of 25K records you are summing the entire set of records. DSum is an aggregate function that operates on all records in a field, subject to criteria, so there can only be one value returned by the function (unless I'm missing something wrt what your query does). To do that 25k times is, well - crazy. Add to that, you are performing the DatePart calculation 25k times. That one may be different for many/all records, so
- get the Dsum value and pass it to the query
- if the datepart calc will be the same for all, then do the same. If not, perhaps build a subset of records according to that calculation and link to it.
The second point may not be necessary if you eliminate 25K DSum calculations.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.