mah_j (10/23/2016)
Currently the index is just on invoiceuid,but because of lookup I test it with other columns too.But for both modes the logical io is high.
The second execution plan is much better, the index eliminates the expensive key lookup.
😎
The main problem now is the dbo.Invoice table, close to 1M rows passed to the join with less than 44K rows out of the join. A filtered covering index for the query would mitigate this, something like this
CREATE NONCLUSTERED INDEX [INDEX_NAME] ON dbo.Invoice(InvoiceUID)
WHERE ([Amount] <= 9999999999.00)
AND ([Amount] >= 0.00)
AND ([VerificationState] <> 0)
AND ([TermID] = 735)
AND ([MerchID] = 302)
INCLUDE
( [Amount]
,[InvoiceNumber]
,[InvoiceDate]
,[VerificationState]
,[TermID]
)
If any of the predicates are passed as parameters then obviously those needs to be moved from the filter clause to the index columns.
Another thing, the TOP(100000) doesn't do any good in this case, suggest you remove it which will eliminate the sort if there is an index with the output sort order on the dbo.Transaction table.