>>>>>Thanks!
>>>>>
>>>>>I can't attach it here and neither can I post it even though I surround it with pre tags. :(
>>>>
>>>>Frank,
>>>>ZIP the .SQLPLAN and attach it as a file here.
>>>
>>>See attached.
>>
>>That looks like the estimated executionplan as its missing the actual numbers. Can you post the performed execution plan?
>>
>>Can you also execute it with the RECOMPILE option? Depending on the parameters passed it might reuse an execution plan that is optimal for one value, but not for another.
>>
>>
>>Walter,
>
>I've attached the Actual Execution Plan.
>
>Will research the RECOMPILE option and try it out.
Yes, this shows the problem. There is simply too much I/O going on. Too many rows being retrieved from the database.
I suspect the problem is in the NOT IN(), in combination with the UNIONs in the subquery.
I'd try to rewrite the query to use
WHERE NOT EXISTS (SELECT 1 FROM CashReceipts INNER JOIN Deposits on crt_depfk = dep_pk AND crt_invfk = ivd_invfk)
AND NOT EXISTS (SELECT 1 FROM receiptdetailszipx INNER JOIN Receipts ON rdz_rctfk = rct_pk AND rdz_invfk = ivd_invfk)
AND NOT EXISTS (SELECT 1 FROM ...)
AND NOT EXISTS (SELECT 1 FROM ...)
Walter,