SQL query produced by Entity Framework very slow

Question

I have the following query being generated by EF:

DECLARE @__term_0 VARCHAR(7) = 'woodrow'
DECLARE @__p_1 INT = 0
DECLARE @__p_2 INT = 15

SELECT [t].[Id], [t].[City], [t].[Contact], [t].[CreatedBy], [t].[CreatedOn], [t].[Deposit], [t].[Email], [t].[Fax], [t].[ModifiedBy], [t].[ModifiedOn], [t].[Name], [t].[NearbyCities], [t].[Notes], [t].[PayMethod], [t].[Pets], [t].[Phone], [t].[StateId], [t].[Street], [t].[Taxes], [t].[Utilities], [t].[VacateNotice], [t].[Website], [t].[Zip], [h0].[Id], [h0].[Cost], [h0].[CreatedBy], [h0].[CreatedOn], [h0].[HousingId], [h0].[ModifiedBy], [h0].[ModifiedOn], [h0].[Notes], [t0].[Id], [t0].[AdminOverhead], [t0].[BillCallBackRate], [t0].[BillHolidayRate], [t0].[BillOnCallRate], [t0].[BillOvertimeRate], [t0].[BillRegularRate], [t0].[BlsNeeded], [t0].[CallBackRule], [t0].[CanceledCandidateDetail], [t0].[CanceledCompanyDetail], [t0].[CanceledDate], [t0].[CanceledReason], [t0].[CandidateId], [t0].[ComplianceInfo], [t0].[ComplianceUserId], [t0].[ContractNotes], [t0].[CreatedBy], [t0].[CreatedOn], [t0].[DrugscreenZip], [t0].[EmailInvoice], [t0].[EndDate], [t0].[Eval1], [t0].[Eval2], [t0].[Eval3], [t0].[Eval4], [t0].[Eval5], [t0].[EvalComments], [t0].[EvalDate], [t0].[EvalRecommend], [t0].[FormType], [t0].[HolidayRule], [t0].[HourlyGuarantee], [t0].[HoursPerWeek], [t0].[HousingCost], [t0].[HousingId], [t0].[HousingPaid], [t0].[HousingPayOutDescription], [t0].[IsCancellationComplete], [t0].[IsExtension], [t0].[IsHousingAllowance], [t0].[IsOnboardComplete], [t0].[IsPaySplitPercentage], [t0].[IsPending], [t0].[JobId], [t0].[ModifiedBy], [t0].[ModifiedOn], [t0].[NeverWorked], [t0].[NumberOfWeeks], [t0].[OverHeadOverride], [t0].[OvertimeRule], [t0].[PONumber], [t0].[RtoRequestForContract], [t0].[SecondaryPaySplit], [t0].[ServiceNotes], [t0].[StartDate], [t0].[Status], [t0].[TaxOverride], [t0].[ThirdPaySplit], [t0].[TimesheetNotes], [t0].[VoidOverHeadFee], [t0].[VoidTaxOverride], [t0].[WageCallBackRate], [t0].[WageOnCallRate], [t0].[WageOvertimeRate], [t0].[WageRegularRate], [t0].[Id0], [t0].[ActiveStateLicenseId], [t0].[AvailableDate], [t0].[City], [t0].[CreatedBy0], [t0].[CreatedOn0], [t0].[DateOfBirth], [t0].[Email], [t0].[EmployeeId], [t0].[FirstName], [t0].[HasWorked], [t0].[HomeStateId], [t0].[HoursPreference], [t0].[HousingPreferences], [t0].[LastName], [t0].[Latitude], [t0].[Longitude], [t0].[MiddleName], [t0].[ModifiedBy0], [t0].[ModifiedOn0], [t0].[Pets], [t0].[Phone], [t0].[ReferredBy], [t0].[RepId], [t0].[ReqTimeOffDesc], [t0].[ReqTimeOffEndDate], [t0].[ReqTimeOffStartDate], [t0].[SecondaryPhone], [t0].[ShiftPreference], [t0].[Smoker], [t0].[Source], [t0].[Ssn], [t0].[StateId], [t0].[StatePreference], [t0].[Status0], [t0].[StatusId], [t0].[Street], [t0].[SubmissionBio], [t0].[TechBio], [t0].[TravelType], [t0].[TravelsWithFamily], [t0].[UnsubscribeEmail], [t0].[YearsExperience], [t0].[Zip], [t0].[Id1], [t0].[AppUserId], [t0].[CandidateCommission], [t0].[CompanyCommission], [t0].[CreatedBy1], [t0].[CreatedOn1], [t0].[ModifiedBy1], [t0].[ModifiedOn1], [t0].[Name], [t0].[Id2], [t0].[AgreementContactId], [t0].[BillCallBackRate0], [t0].[BillHolidayRate0], [t0].[BillOnCallRate0], [t0].[BillOvertimeRate0], [t0].[BillRegularRate0], [t0].[CompanyId], [t0].[CreatedBy2], [t0].[CreatedOn2], [t0].[EmailSenderId], [t0].[EmailSentDate], [t0].[EndDate0], [t0].[Equipment], [t0].[HoursPerWeek0], [t0].[IsCertRequired], [t0].[LocalDistance], [t0].[LocalOnly], [t0].[ModalityId], [t0].[ModifiedBy2], [t0].[ModifiedOn2], [t0].[Notes], [t0].[NumberOfPositions], [t0].[NumberOfWeeks0], [t0].[PositionType], [t0].[RepAssignedId], [t0].[RepId0], [t0].[Requirements], [t0].[Shift], [t0].[SignerContactId], [t0].[StartDate0], [t0].[Status1], [t0].[UnitOrDepartment], [t0].[VMSCustomDiscountId], [t0].[VendorManagerId], [t0].[Id3], [t0].[AcuteCare], [t0].[BedSize], [t0].[BillingNotes], [t0].[City0], [t0].[CompanySystemId], [t0].[CompanyType], [t0].[CreatedBy3], [t0].[CreatedOn3], [t0].[CredentialNotes], [t0].[DailyTimesheetInterval], [t0].[FinanceChargeNotes], [t0].[FinanceChargeRate], [t0].[JobNotes], [t0].[Latitude0], [t0].[LocalDistance0], [t0].[LocalOnly0], [t0].[Longitude0], [t0].[ModifiedBy3], [t0].[ModifiedOn3], [t0].[Name0], [t0].[PaymentTerms], [t0].[Phone0], [t0].[RepId1], [t0].[StateId0], [t0].[Street0], [t0].[VendorManagerId0], [t0].[Website], [t0].[Zip0]
FROM (
    SELECT [h].[Id], [h].[City], [h].[Contact], [h].[CreatedBy], [h].[CreatedOn], [h].[Deposit], [h].[Email], [h].[Fax], [h].[ModifiedBy], [h].[ModifiedOn], [h].[Name], [h].[NearbyCities], [h].[Notes], [h].[PayMethod], [h].[Pets], [h].[Phone], [h].[StateId], [h].[Street], [h].[Taxes], [h].[Utilities], [h].[VacateNotice], [h].[Website], [h].[Zip]
    FROM [Housings] AS [h]
    WHERE (((((@__term_0 = N'') OR (CHARINDEX(@__term_0, [h].[Name]) > 0)) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, [h].[City]) > 0))) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, [h].[NearbyCities]) > 0))) OR EXISTS (
        SELECT 1
        FROM [Placements] AS [p]
        INNER JOIN [Candidates] AS [c] ON [p].[CandidateId] = [c].[Id]
        INNER JOIN [Jobs] AS [j] ON [p].[JobId] = [j].[Id]
        INNER JOIN [Companies] AS [c0] ON [j].[CompanyId] = [c0].[Id]
        INNER JOIN [Reps] AS [r] ON [c].[RepId] = [r].[Id]
        WHERE ([h].[Id] = [p].[HousingId]) AND (((((@__term_0 = N'') OR (CHARINDEX(@__term_0, [c].[FirstName]) > 0)) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, [c].[LastName]) > 0))) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, [c0].[Name]) > 0))) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, [r].[Name]) > 0))))) OR ((@__term_0 = N'') OR (CHARINDEX(@__term_0, REPLACE(REPLACE(REPLACE(REPLACE([h].[Phone], N' ', N''), N'(', N''), N')', N''), N'-', N'')) > 0))
    ORDER BY [h].[CreatedBy] DESC, [h].[StateId], [h].[City], [h].[Name]
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]
LEFT JOIN [HousingPricingTiers] AS [h0] ON [t].[Id] = [h0].[HousingId]
LEFT JOIN (
    SELECT [p0].[Id], [p0].[AdminOverhead], [p0].[BillCallBackRate], [p0].[BillHolidayRate], [p0].[BillOnCallRate], [p0].[BillOvertimeRate], [p0].[BillRegularRate], [p0].[BlsNeeded], [p0].[CallBackRule], [p0].[CanceledCandidateDetail], [p0].[CanceledCompanyDetail], [p0].[CanceledDate], [p0].[CanceledReason], [p0].[CandidateId], [p0].[ComplianceInfo], [p0].[ComplianceUserId], [p0].[ContractNotes], [p0].[CreatedBy], [p0].[CreatedOn], [p0].[DrugscreenZip], [p0].[EmailInvoice], [p0].[EndDate], [p0].[Eval1], [p0].[Eval2], [p0].[Eval3], [p0].[Eval4], [p0].[Eval5], [p0].[EvalComments], [p0].[EvalDate], [p0].[EvalRecommend], [p0].[FormType], [p0].[HolidayRule], [p0].[HourlyGuarantee], [p0].[HoursPerWeek], [p0].[HousingCost], [p0].[HousingId], [p0].[HousingPaid], [p0].[HousingPayOutDescription], [p0].[IsCancellationComplete], [p0].[IsExtension], [p0].[IsHousingAllowance], [p0].[IsOnboardComplete], [p0].[IsPaySplitPercentage], [p0].[IsPending], [p0].[JobId], [p0].[ModifiedBy], [p0].[ModifiedOn], [p0].[NeverWorked], [p0].[NumberOfWeeks], [p0].[OverHeadOverride], [p0].[OvertimeRule], [p0].[PONumber], [p0].[RtoRequestForContract], [p0].[SecondaryPaySplit], [p0].[ServiceNotes], [p0].[StartDate], [p0].[Status], [p0].[TaxOverride], [p0].[ThirdPaySplit], [p0].[TimesheetNotes], [p0].[VoidOverHeadFee], [p0].[VoidTaxOverride], [p0].[WageCallBackRate], [p0].[WageOnCallRate], [p0].[WageOvertimeRate], [p0].[WageRegularRate], [c1].[Id] AS [Id0], [c1].[ActiveStateLicenseId], [c1].[AvailableDate], [c1].[City], [c1].[CreatedBy] AS [CreatedBy0], [c1].[CreatedOn] AS [CreatedOn0], [c1].[DateOfBirth], [c1].[Email], [c1].[EmployeeId], [c1].[FirstName], [c1].[HasWorked], [c1].[HomeStateId], [c1].[HoursPreference], [c1].[HousingPreferences], [c1].[LastName], [c1].[Latitude], [c1].[Longitude], [c1].[MiddleName], [c1].[ModifiedBy] AS [ModifiedBy0], [c1].[ModifiedOn] AS [ModifiedOn0], [c1].[Pets], [c1].[Phone], [c1].[ReferredBy], [c1].[RepId], [c1].[ReqTimeOffDesc], [c1].[ReqTimeOffEndDate], [c1].[ReqTimeOffStartDate], [c1].[SecondaryPhone], [c1].[ShiftPreference], [c1].[Smoker], [c1].[Source], [c1].[Ssn], [c1].[StateId], [c1].[StatePreference], [c1].[Status] AS [Status0], [c1].[StatusId], [c1].[Street], [c1].[SubmissionBio], [c1].[TechBio], [c1].[TravelType], [c1].[TravelsWithFamily], [c1].[UnsubscribeEmail], [c1].[YearsExperience], [c1].[Zip], [r0].[Id] AS [Id1], [r0].[AppUserId], [r0].[CandidateCommission], [r0].[CompanyCommission], [r0].[CreatedBy] AS [CreatedBy1], [r0].[CreatedOn] AS [CreatedOn1], [r0].[ModifiedBy] AS [ModifiedBy1], [r0].[ModifiedOn] AS [ModifiedOn1], [r0].[Name], [j0].[Id] AS [Id2], [j0].[AgreementContactId], [j0].[BillCallBackRate] AS [BillCallBackRate0], [j0].[BillHolidayRate] AS [BillHolidayRate0], [j0].[BillOnCallRate] AS [BillOnCallRate0], [j0].[BillOvertimeRate] AS [BillOvertimeRate0], [j0].[BillRegularRate] AS [BillRegularRate0], [j0].[CompanyId], [j0].[CreatedBy] AS [CreatedBy2], [j0].[CreatedOn] AS [CreatedOn2], [j0].[EmailSenderId], [j0].[EmailSentDate], [j0].[EndDate] AS [EndDate0], [j0].[Equipment], [j0].[HoursPerWeek] AS [HoursPerWeek0], [j0].[IsCertRequired], [j0].[LocalDistance], [j0].[LocalOnly], [j0].[ModalityId], [j0].[ModifiedBy] AS [ModifiedBy2], [j0].[ModifiedOn] AS [ModifiedOn2], [j0].[Notes], [j0].[NumberOfPositions], [j0].[NumberOfWeeks] AS [NumberOfWeeks0], [j0].[PositionType], [j0].[RepAssignedId], [j0].[RepId] AS [RepId0], [j0].[Requirements], [j0].[Shift], [j0].[SignerContactId], [j0].[StartDate] AS [StartDate0], [j0].[Status] AS [Status1], [j0].[UnitOrDepartment], [j0].[VMSCustomDiscountId], [j0].[VendorManagerId], [c2].[Id] AS [Id3], [c2].[AcuteCare], [c2].[BedSize], [c2].[BillingNotes], [c2].[City] AS [City0], [c2].[CompanySystemId], [c2].[CompanyType], [c2].[CreatedBy] AS [CreatedBy3], [c2].[CreatedOn] AS [CreatedOn3], [c2].[CredentialNotes], [c2].[DailyTimesheetInterval], [c2].[FinanceChargeNotes], [c2].[FinanceChargeRate], [c2].[JobNotes], [c2].[Latitude] AS [Latitude0], [c2].[LocalDistance] AS [LocalDistance0], [c2].[LocalOnly] AS [LocalOnly0], [c2].[Longitude] AS [Longitude0], [c2].[ModifiedBy] AS [ModifiedBy3], [c2].[ModifiedOn] AS [ModifiedOn3], [c2].[Name] AS [Name0], [c2].[PaymentTerms], [c2].[Phone] AS [Phone0], [c2].[RepId] AS [RepId1], [c2].[StateId] AS [StateId0], [c2].[Street] AS [Street0], [c2].[VendorManagerId] AS [VendorManagerId0], [c2].[Website], [c2].[Zip] AS [Zip0]
    FROM [Placements] AS [p0]
    INNER JOIN [Candidates] AS [c1] ON [p0].[CandidateId] = [c1].[Id]
    INNER JOIN [Reps] AS [r0] ON [c1].[RepId] = [r0].[Id]
    INNER JOIN [Jobs] AS [j0] ON [p0].[JobId] = [j0].[Id]
    INNER JOIN [Companies] AS [c2] ON [j0].[CompanyId] = [c2].[Id]
) AS [t0] ON [t].[Id] = [t0].[HousingId]
ORDER BY [t].[CreatedBy] DESC, [t].[StateId], [t].[City], [t].[Name], [t].[Id], [h0].[Id], [t0].[Id], [t0].[Id0], [t0].[Id1], [t0].[Id2], [t0].[Id3]

It takes about 40 seconds to run which is a lot longer than I need it to. I suspect I just need better indexing but I'm not sure how to analyze my data plan to get the proper insights.

Here's the XML for the data plan: https://jumpshare.com/s/zkTdyVn2aYMXM5A8vJuF (The XML is copied directly from Azure Data Studio. For some reason the XML wouldn't validate with Paste the Plan)

How can I improve this query's performance?

Answer 1

It appears that the query execution is performing a table scan (clustered index scan) that accesses all 42 million rows from the Placement table, only to return 3600 matching rows.

I suspect that that you are missing an index. Try creating index:

CREATE INDEX ON Placements(HousingId)

or maybe:

CREATE INDEX ON Placements(HousingId) INCLUDE(JobId, CandidateId)

The INCLUDE(...) portion is optional, but might save a clustered index seek step, yielding a slight additional performance gain.

You should also verify that Housings.id and Placements.HousingId have the same data type.

There might also be a lesser issue with the join to HousingPricingTiers. Unfortunately, the query contained in the execution plan XML is truncated and I cannot see the details.