OK here you go all the gory details, it all looks OK to me. This is view...
ALTER VIEW [dbo].[vwConsolidatedCLT]
SELECT CLT.TransID, CLT.LedgerDate, CON.ClAccountID, CLT.ClAccountID AS subclaccountid,
CLT.LineNumber, CLT.MovementType, CLT.MovementSource, CLT.CCYCode, CLT.Amount,
CLT.Balance, CLT.Reference, CLT.Narrative, CLT.DisplayToClient, CLT.Capital,
CLT.ProductCapital, CLT.UserID, CLT.DateCreated, CLT.LedgerSource, CLT.LedgerSourceID,
CLT.FromCCYCode, CLT.ToCCYCode, CLT.BaseCCYCode, CLT.FXRate, CLT.RestrictSweepUntil,
CLT.AdjustmentUserID, PD.ProductType,COALESCE(PD.ProductDisplayName,PD.ProductType) as ProductDisplayName,
FROM ClientAccount.dbo.CashLedgerTransactions as CLT
INNER JOIN ClientAccount.dbo.Consolidate as CON
ON CON.SubClAccountID = CLT.ClAccountID
LEFT OUTER JOIN Discovery.dbo.ProductDetails as PD
ON PD.ClAccountID = CON.SubClAccountID
and this is the query...
SELECT [TransID], [LedgerDate], [ClAccountID], [subclaccountid], [LineNumber], [MovementType], [MovementSource],
[CCYCode], [Amount], [Balance], [Reference], [Narrative], [DisplayToClient], [Capital], [ProductCapital],
[UserID], [DateCreated], [LedgerSource], [LedgerSourceID], [FromCCYCode], [ToCCYCode], [BaseCCYCode], [FXRate],
[RestrictSweepUntil], [AdjustmentUserID], [ProductType], [ProductDisplayName], [ExcludeFromCalc]
WHERE [ClAccountID] = @0 AND [LedgerDate] > @1 AND [LedgerDate] <= @2 AND
([MovementType] Like @3 OR [MovementType] = @4) AND [ExcludeFromCalc] = @5
I'm heading for the OPTIMIZE FOR hint for now.
The density for each index is similar so the incorrect assumption is that the number of rows from consolidate returns more than the distinct ledgerdates in the range. From the stats on consolidate the largest number eq to any value is 96, but the majority are less than 24.
The stats on the cashledger transaction table indicate an average of 30K-50K rows per ledgerdate and 2K-4K per account.
So a worst case account query could get about (96*50K) 384K rows which would mean that any date range more than 12 days would be better by account, the average case would be (24*3K) 72K rows which would make a date range of more than 3 days less efficient. It is usual for the date range to be a month or two.