SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) - ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0) FROM (Credit) M WITH(NOLOCK) INNER JOIN (Transaction) T4 WITH(NOLOCK) ON M.f_accounts=T4.f_accounts AND ( T4.f_time>=M.f_time OR M.f_time IS NULL ) AND T4.f_other IS NULL AND (T4.f_in=12 OR T4.f_out=12)
After adding an index, one of the queries that originally finishes within 1s now takes around 12s. It seems that the index is now seeked by the query with bad estimation of only 1 row to be read. No other indexes are present in both Credit and Transaction tables.
CREATE NONCLUSTERED INDEX IX_Credit_f_time ON Credit (f_time) INCLUDE (f_accounts,f_credit)
Above is the index added to the table.
Plan without index, takes 1s
Plan with the index, takes 12s. The highlighted parts are number of rows read, estimated number of rows and estimated number of rows to be read respectively.
Statistics are updated with fullscan but results are still the same.
From my understanding the f_time column is pretty concentrated, hash joining the table once would be much more efficient than seeking multiple times.
Full Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJjwvXBA_