I am using SQL Server 2016, and trying to join a table onto itself, but the query is very slow, and it seems its because SQL Server is refusing to use the indexes.

If you'll excuse the crudity of this example, I have a table like this...

TABLE [Balances] (
    account_id NCHAR(20),
    balance_date DATE,
    balance INT,
    next_balance_date DATE

... and I essentially want to do this query:

SELECT [t1].*, [t2].[balance] [next_balance]
FROM [Balances] [t1]
LEFT JOIN [Balances] [t2]
ON [t1].[next_balance_date] = [t2].[balance_date]
AND [t1].[account_id] = [t2].[account_id]

The problem is that no matter what index I try to put onto this table, SQL Server insists on doing a hash match. Which is slow on this large database.

And I currently have the following indexes:

NONCLUSTERED INDEX [idx_acc_date] ON [Balances]​ ([account_id] ASC, [balance_date] ASC)
NONCLUSTERED INDEX [idx_acc_nextdate] ON [Balances]​ ([account_id] ASC, [next_balance_date] ASC)​

What indexes should I be creating to speed-up this query?

(As a small note on the data - all of the balance_dates are as at month end, and therefore the same goes for the next_balance_date. Also, each account_id only has one entry at each balance_date.)

  • What's the execution plan? It's not using the indexes because they're not covering - the select * prevents them from being so - also neither index contains all the columns in play in your query. Aug 8 '16 at 1:18
  • Thanks sp_BlitzErik. I was expecting indexes to be used for the JOIN, which is the part of the query that is taking the most time, as it is CPU-bound. The table itself is mostly cached in RAM. Am I mistaken that SQL Server can use the indexes for the JOIN?
    – robzy
    Aug 8 '16 at 1:41
  • Why are all columns nullable, are there no mandatory information?
    – Lennart
    Aug 9 '16 at 11:38

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.


  1. You create clustered index on only one column. Technically, you can do it on multiple, but the main goal to have it as short as possible.
  2. If you created clustered index on account_id you DO NOT NEED any other indexes on that column.
  3. I can guess that your performance improved because before there was no Clustered index and indexes were so bad that SQL decided to do full table scan instead.
  4. Suggestion: read a book about Indexes, their differences and how they work.
  • Thank you very much for your answer. I created a clustered index on (account_id, date, next_date) and initial tests showed much better performance. I already had this index as a non-clustered index, though, and I don't understand why the clustered index created such a performance boost when everything is in RAM anyway.
    – robzy
    Aug 9 '16 at 6:27
  • See additional notes. and mark question as answered. Aug 9 '16 at 11:14
  • @robzy, assuming everything happens in RAM, there is still a lot of processing going on. An infinite loop in RAM still takes as long as one on disk. :)
    – datagod
    Aug 9 '16 at 13:56

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.