The number of logical reads explodes with one additional internal join

Question

I have two tables that look almost the same. When I choose from any of them, my logical readings are around 8.000-10.000. But when I join them, I get about 380,000 logical readings.

I am using MS SQL 2012.

I have a problem with the request:

SELECT ac.ID AS AccountID ,ab.Balance ,abc.BalanceInAccountCurrency FROM dbo.Dates d INNER JOIN dbo.Accounts ac ON d.[Date] BETWEEN ac.CreationDate AND ac.ClosureDate INNER JOIN dbo.AccountBalances ab ON ab.AccountID = ac.ID AND d.[Date] BETWEEN ab.CreationDate AND ab.ClosureDate INNER JOIN dbo.AccountBalancesInAccountCurrency abc ON abc.AccountID = ac.ID AND d.[Date] BETWEEN abc.CreationDate AND abc.ClosureDate WHERE d.[Date] = DATEFROMPARTS(2017,06,20); 

When I join only AccountBalances as follows:

 SELECT ac.ID AS AccountID ,ab.Balance FROM dbo.Dates d INNER JOIN dbo.Accounts ac ON d.[Date] BETWEEN ac.CreationDate AND ac.ClosureDate INNER JOIN dbo.AccountBalances ab ON ab.AccountID = ac.ID AND d.[Date] BETWEEN ab.CreationDate AND ab.ClosureDate WHERE d.[Date] = DATEFROMPARTS(2017,06,20); 

And I get similar results when I only join AccountBalancesInAccountCurrency.

My primary key / clustered index looks like in both tables:

 ALTER TABLE [dbo].[AccountBalances] ADD CONSTRAINT [PK_AccountBalances] PRIMARY KEY CLUSTERED ( ClosureDate DESC, CreationDate DESC, AccountID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

What am I doing wrong?

Execution plans

Both tables

Only AccountBalancesInAccountCurrency

AccountBalances only

Additional Information

The query result is 170460 lines, and the total number of logical reads is 376,000. But if I use the "top 170,000" in my query, then the total number of logical reads is only 44,000. IO statistics look like this:

And the execution plan is as follows

Answer 1

I found a solution for my problem.

1. AccountBalances contains balances in the bank currency, which is DKK, and AccountBalancesInAccountCurrency contains balances in the account currency, as well as in DKK. Most currencies are DCK and exist in both tables. Therefore, I removed all DKK balances from AccountBalancesInAccountCurrency and made a left join to this table.

2. I also changed the primary key to AccountBalancesInAccountCurrency:

 ALTER TABLE [dbo].[AccountBalances] ADD CONSTRAINT [PK_AccountBalances] PRIMARY KEY CLUSTERED ( ClosureDate DESC, CreationDate DESC, AccountID ASC ) 

to:

 ALTER TABLE [dbo].[AccountBalancesInAccountCurrency] ADD CONSTRAINT [PK_AccountBalancesInAccountCurrency] PRIMARY KEY CLUSTERED ( [AccountID] ASC, [ClosureDate] DESC ) 

Now my IO looks like this:

and my execution plan is like that .

Thanks for all the comments that led me on my way.

Happy Holidays!