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 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.
Full Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJjwvXBA_
Backup Execution Plan: https://pastebin.com/XLwAGXSf