1
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.

enter image description here

Plan without index, takes 1s enter image description here

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.

enter image description hereenter image description here 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_

Backup Execution Plan: https://pastebin.com/XLwAGXSf

4
  • 2
    The link to your plan returns "id not found". Why did you put the join column (f_accounts) in the includes rather than in the key of the index? Was it a missing index suggestion? Jul 21 '21 at 13:43
  • Hi, thanks for pointing out, there's an underscore at the end of the address that wasn't hyperlinked properly. Added the XML to pastebin as a backup. It was a missing index suggestion.
    – JieLong
    Jul 21 '21 at 14:30
  • As Erik mentioned, try the index with the f_accounts field in the key (and try both with it in first and second position) and see if that helps. Also, I don't see in your query where the f_credit field is used, is that the PK of the table or is this index for more than just this one query?
    – BCM
    Jul 21 '21 at 14:49
  • @BCM Having f_accounts in the first position does help, it generated a different execution plan. But then again, the query works fine without any index in the first place, I wonder why why would it choose to use that index at all.
    – JieLong
    Jul 21 '21 at 16:47
1
+50

I believe this index is confusing SQL Server. The join conditions include

T4.f_time>=M.f_time

This type of join predicate is generally hard for SQL Server to reason about (greater or smaller, with columns on both sides). The index makes things worse, because it's first key column is f_time and other columns are only included. So for every row from Transaction table it seeks all rows where T4.f_time>=M.f_time and only then applies other predicates. And here it goes wrong, because optimizer guesses that for every seek there will be one row read. Where actually it's 57237 on average (89290306 rows read divided by 1560 executions). This is easily visible in the execution plan:

enter image description here

In my opinion the index should be dropped and following one created (as suggested by others in comments):

CREATE NONCLUSTERED INDEX IX_Credit
ON Credit
(f_accounts,f_time)

That way a seek can be done on both of these columns and it should be much more selective. Do note the order of the columns is important here.

As a general note I try to put columns where equality predicate is used first and columns with inequality as last in indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.