Performance Improvements

[billdeitrick]

I'm noticing that the MailChimp sync frequently runs the following query (truncated since it was pulled from query store):

/*
This query text was retrieved from showplan XML, and may be truncated.
*/

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IsSystem] AS [IsSystem], 
    [Extent1].[RecordTypeValueId] AS [RecordTypeValueId], 
    [Extent1].[RecordStatusValueId] AS [RecordStatusValueId], 
    [Extent1].[RecordStatusLastModifiedDateTime] AS [RecordStatusLastModifiedDateTime], 
    [Extent1].[RecordStatusReasonValueId] AS [RecordStatusReasonValueId], 
    [Extent1].[ConnectionStatusValueId] AS [ConnectionStatusValueId], 
    [Extent1].[ReviewReasonValueId] AS [ReviewReasonValueId], 
    [Extent1].[IsDeceased] AS [IsDeceased], 
    [Extent1].[TitleValueId] AS [TitleValueId], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[NickName] AS [NickName], 
    [Extent1].[MiddleName] AS [MiddleName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[SuffixValueId] AS [SuffixValueId], 
    [Extent1].[PhotoId] AS [PhotoId], 
    [Extent1].[BirthDay] AS [BirthDay], 
    [Extent1].[BirthMonth] AS [BirthMonth], 
    [Extent1].[BirthYear] AS [BirthYear], 
    [Extent1].[Age] AS [Age], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[MaritalStatusValueId] AS [MaritalStatusValueId], 
    [Extent1].[AnniversaryDate] AS [AnniversaryDate], 
    [Extent1].[GraduationYear] AS [GraduationYear], 
    [Extent1].[GivingId] AS [GivingId], 
    [Extent1].[GivingLeaderId] AS [GivingLeaderId], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[IsEmailActive] AS [IsEmailActive], 
    [Extent1].[EmailNote] AS [EmailNote], 
    [Extent1].[EmailPreference] AS [EmailPreference], 
    [Extent1].[CommunicationPreference] AS [CommunicationPreference], 
    [Extent1].[ReviewReasonNote] AS [ReviewReasonNote], 
    [Extent1].[InactiveReasonNote] AS [InactiveReasonNote], 
    [Extent1].[SystemNote] AS [SystemNote], 
    [Extent1].[ViewedCount] AS [ViewedCount], 
    [Extent1].[TopSignalColor] AS [TopSignalColor], 
    [Extent1].[TopSignalIconCssClass] AS [TopSignalIconCssClass], 
    [Extent1].[TopSignalId] AS [TopSignalId], 
    [Extent1].[AgeClassification] AS [AgeClassification], 
    [Extent1].[PrimaryFamilyId] AS [PrimaryFamilyId], 
    [Extent1].[PrimaryCampusId] AS [PrimaryCampusId], 
    [Extent1].[IsLockedAsChild] AS [IsLockedAsChild], 
    [Extent1].[DeceasedDate] AS [DeceasedDate], 
    [Extent1].[ContributionFinancialAccountId] AS [ContributionFinancialAccountId], 
    [Extent1].[AccountProtectionProfile] AS [AccountProtectionProfile], 
    [Extent1].[PreferredLanguageValueId] AS [PreferredLanguageValueId], 
    [Extent1].[ReminderCount] AS [ReminderCount], 
    [Extent1].[RaceValueId] AS [RaceValueId], 
    [Extent1].[EthnicityValueId] AS [EthnicityValueId], 
    [Extent1].[BirthDateKey] AS [BirthDateKey], 
    [Extent1].[AgeBracket] AS [AgeBracket], 
    [Extent1].[FirstNamePronunciationOverride] AS [FirstNamePronunciationOverride], 
    [Extent1].[NickNamePronunciationOverride] AS [NickNamePronunciationOverride], 
    [Extent1].[LastNamePronunciationOverride] AS [LastNamePronunciationOverride], 
    [Extent1].[PronunciationNote] AS [PronunciationNote], 
    [Extent1].[PrimaryAliasId] AS [PrimaryAliasId], 
    [Extent1].[DaysUntilBirthday] AS [DaysUntilBirthday], 
    [Extent1].[GivingGroupId] AS [GivingGroupId], 
    [Extent1].[BirthDate] AS [BirthDate], 
    [Extent1].[DaysUntilAnniversary] AS [DaysUntilAnniversary], 
    [Extent1].[CreatedDateTime] AS [CreatedDateTime], 
    [Extent1].[ModifiedDateTime] AS [ModifiedDateTime], 
    [Extent1].[CreatedByPersonAliasId] AS [CreatedByPersonAliasId], 
    [Extent1].[ModifiedByPersonAliasId] AS [ModifiedByPersonAliasId], 
    [Extent1].[Guid] AS [Guid], 
    [Extent1].[ForeignId] AS [ForeignId], 
    [Extent1].[ForeignGuid] AS [ForeignGuid], 
    [Extent1].[ForeignKey] AS [ForeignKey]
    FROM [dbo].[Person] AS [Extent1]
    WHERE ([Extent1].[RecordTypeValueId] IS NOT NULL) AND ( NOT ((@p__linq__0 = [Extent1].[RecordTypeValueId]) AND (0 = (CASE WHEN ([Extent1].[RecordTypeValueId] IS NULL) THEN cast(1 a

SQL is suggesting an unreasonable index, that seems like it has 3/4 of the columns in the table:

The Query Processor estimates that implementing the following index could improve the query cost by 67.7576%.
*/

/*
USE [rockprod]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Person] ([IsDeceased],[ForeignKey],[RecordTypeValueId],[Guid])
INCLUDE ([IsSystem],[RecordStatusValueId],[RecordStatusReasonValueId],[ConnectionStatusValueId],[TitleValueId],[FirstName],[NickName],[MiddleName],[LastName],[SuffixValueId],[PhotoId],[BirthDay],[BirthMonth],[BirthYear],[Gender],[MaritalStatusValueId],[AnniversaryDate],[GivingGroupId],[Email],[IsEmailActive],[EmailNote],[SystemNote],[ViewedCount],[CreatedDateTime],[ModifiedDateTime],[CreatedByPersonAliasId],[ModifiedByPersonAliasId],[EmailPreference],[InactiveReasonNote],[ReviewReasonValueId],[ReviewReasonNote],[GraduationYear],[ForeignGuid],[ForeignId],[RecordStatusLastModifiedDateTime],[CommunicationPreference],[TopSignalColor],[TopSignalIconCssClass],[TopSignalId],[AgeClassification],[PrimaryFamilyId],[IsLockedAsChild],[DeceasedDate],[GivingLeaderId],[BirthDate],[ContributionFinancialAccountId],[PrimaryCampusId],[GivingId],[PreferredLanguageValueId],[AccountProtectionProfile],[ReminderCount],[RaceValueId],[EthnicityValueId],[BirthDateKey],[AgeBracket],[Age],[FirstNamePronunciationOverride],[NickNamePronunciationOverride],[LastNamePronunciationOverride],[PronunciationNote],[PrimaryAliasId])
GO
*/

This doesn't seem like a resonable index, but highlights the fact that we're pulling a large number of fields from SQL one record at a time, and we probably don't need the vast majority of these fields at all. On one example system, this query caused ~ 17K full table scans of the person table in one hour.

From a quick glance at the job code, it seems like that query might be coming from this general area:

MailChimp/com.bemaservices.MailChimp/Utility/MailChimpApi.cs

Line 555 in 1f47b7f

person = personService.Queryable().AsNoTracking().Where( p => p.ForeignKey == mailchimpForeignKey ).FirstOrDefault();

Since we probably only need a few of these properties to be actually loaded into the entity, I'm wondering if we can lazy load properties instead somehow to avoid causing SQL to resort to full table scans. Maybe this will improve performance when there are a large number of records to sync.

Could we consider looking at lazy loading here to see if that would improve sync performance?