>Walter,
>
>>
>>Yes, this shows the problem. There is simply too much I/O going on. Too many rows being retrieved from the database.
>>
>
>Could you explain how to interpret the execution plan as you did, please?

If you look at the executionplan of your original query. You'll see as has Match (Right anti Semi Join) costing 25% of the query.
The operator cost is 25% and this is clearly the bottleneck. It takes almost 2 million rows as input and only puts out 9674.

A lot of (logical) I/O is needed to retrieve those 2 million rows from the database and a lot of CPU is needed to weed out the unneeded rows (NOT IN(..) operator). So its critical that you need to reduce the I/O needed to get the information you need to do the same work.

It currently gets 1.36 million records from the IX_invoice index (costs = 16%)

Its impossible for me to tell you the exact solution to the problems as other factors are involved, like whether the rows are already cached or need to be retreived from disk (physical I/O), clustered indexes and the hardware config of your server.

But I got a few suggestions:

1. I''m affraid that the you cannot do much about the sheer number of records (1.36 million) retrieved from the invoiceDetails table, without additional measures. They are needed to determine which ones need to be weeded out and which to include.

It might be better to use the Invoices table (I guess there must be one Invoices table with inv_pk PK), so it becomes more like

SELECT inv_pk, inv_total FROM Invoices WHERE inv_pk NOT IN (...)

Assuming that there are a lot less invoice records rather than details, this should resolve the I/O problems in retrieving the invoice numbers.

If your invoices table does not include an inv_total column, you might do a (SELECT SUM(inv_amt) FROM InvoiceDetails WHERE ivd_invfk = inv_pk) as inv_total. Since this will be executed on result of the hash match anti join, it should be fast enough.

2. Create exact compound indexes (indexes consisting out more than one field) on the tables with joins:
CashReciepts (crt_invfk, crt_depfk)
Deposits (dep_date, dep_pk)
ReceiptDetailsZipx(rdz_invfk, rdz_rctfk)
Receipts(rct_date, rct_pk)
etc...

This will create full covarage indexes and general speed up your query.

3. Determine the bottleneck by elimination.
Though the executionplan tells you a lot of the data being retrieved and processed, it does not neccesarily say something about the real bottleneck. Sometimes its something that you cannot read from it. Then its wise to take parts out of the query, run it and record the time difference. For example, you could easily take out the SELECTS in the NOT IN () subquery one by one and see which one makes the difference. Then you can concentrate on that part of the query.

4. Run you queries by cleaning the buffers (DBCC dropcleanbuffers) to force SQL server to read the data from disk, which is most likely far closer to practise than if all the data is already cached.





Hope this helps

Walter,