the estimated row is much below the actual, stats are up to date

Louie.Al 2016-07-13 20:18:55

/storage/attachments/1657-Plan_innerjoint.pesession
Aaron Bertrand 2016-07-14 16:02:10
Since it's used in a join operation, have you considered adding InvoiceDetailTypeID as a key column to the index dbo.CIS_ClientPricingProfile.IX_CIS_ClientPricingProfile_SelectChargePriceItem? (Maybe some of the other columns could just be INCLUDE.) Also there are four columns in dbo.Core_Changes that could be added to the IX_CORE_Charges__missing_76528 index as INCLUDE columns. These two changes will eliminate the two key lookups, and perhaps moving AccountID to the front of the list in [PK_CORE_Accounts] (or making a separate supporting index) will eliminate the Sort. It is not quite clear to me what is causing the estimate to be 1, but I suspect those other three changes will help the performance substantially.
Louie.Al 2016-07-14 16:11:27
thank you Aaron, I did the following last night, and that changed the query from 10 seconds to 0.5 second

CREATE NONCLUSTERED INDEX [IX_CIS_ClientPricingProfile_SelectChargePriceItem_jul_13] ON [dbo].[CIS_ClientPricingProfile]
(
[ClientID] ASC,
[ProcessTypeID] ASC,
[InvoiceDetailTypeId] ASC,
[FeeDescriptionId] ASC,
[TdspId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

CREATE NONCLUSTERED INDEX [IXN_CORE_Charges_InvoiceId_Canceled_RolledBack_IsVoidedFeeAmount_jul_13] ON [dbo].[CORE_Charges]
(
[ClientID] ASC,
[ProcessTypeID] ASC,
[InvoiceDetailTypeId] ASC,
[FeeDescriptionId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80)

July 13, 2016
No Comments