I've been working on this query handed to me by a developer who wanted to reduce the query execution time even further. Currently, it takes almost around 40 secs and outputs 262K records. Based on the actual execution the query cost is more towards the clustered index scan on "CacheClients". However, I am not sure if adding a non clustered covering index would benefit it. Also, I see a table scan at the extreme bottom right of the plan and as per the developer they were testing with an index on that table but they weren't able to figure out an appropriate one that can fit the criteria which is basically they would join on the id fields and run queries based on date period.
USE [ABC] GO /****** Object: View [report].[vw_MonthlyDealAllocations] Script Date: 11/24/2017 9:54:10 PM ******/ --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --CREATE VIEW [report].[vw_MonthlyDealAllocations] as SELECT PeriodStartDate, PeriodEndDate, D.DealID, Dl.DealName, D.StageID, CONVERT(varchar(1), D.StageID) + ' - ' + DS.Stage Stage, DP.Platform, ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID, ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID, CASE WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 1 THEN 'Investment Advisory' WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 2 THEN 'Debt/EP' WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 6 THEN 'HFF Securities' ELSE DM.Department END AS AllocatedDepartment, AO.Name AllocatedOffice, CASE WHEN DP.DisplayWithOffice = 1 THEN AO.Name + ' ' + DP.ShortName ELSE AO.Name END AllocatedOfficeDept, Dl.DepartmentID DealDepartmentID, DX.Department DealDepartment, Dl.OfficeID DealOfficeID, DO.Name DealOffice, CASE WHEN DP.DisplayWithOffice = 1 THEN DO.Name + ' ' + DP.ShortName ELSE DO.Name END DealOfficeDept, DT.DealType, D.OfficeAllocVolumePipeline, D.OfficeAllocVolumeCompleted, D.OfficeAllocFeePipeline, D.OfficeAllocFeeCompleted, D.OfficeAllocVolumePipelineGBP, D.OfficeAllocVolumeCompletedGBP, D.OfficeAllocFeePipelineGBP, D.OfficeAllocFeeCompletedGBP, PTG.GroupName, PT.PropertyType, CASE WHEN C.CountryID <> 1 THEN 'Foreign' ELSE R.Region END AS Region, AP.Address1, C.City, S.StateLong, AP.Zip, Investor.ClientName MSA, Cl.ClientName RollupMSA, ITI.InvestorType PrimaryInvestor, ITC.InvestorType PrimaryClient, P.FirstName + ' ' + P.LastName AS PrimaryProducer, I.FirstName + ' ' + I.LastName AS IL_Producer, D.CountDealCompleted, CASE Dl.isSecuritized WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE '' END AS isSecuritized, CASE Dl.isServiced WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE '' END AS isServiced, --QCount.QtCt, /*IIF (CASE WHEN D.isCompleted = 1 THEN D.VolumeCompleted WHEN (D.isPipeline = 1 OR D.StageID = 0) THEN D.VolumePipeline ELSE 0 END >= 50000000,1,NULL ) AS Over50M, */ IIF(Dl.PortfolioID IS NOT NULL, 1, NULL) AS isPortfolio, --IIF (isMultitransactional.linktype IS NOT NULL,1,NULL) AS isMultiTransactional, DP.PlatformID, P.EmployeeID AS PrimaryProducerID, I.EmployeeID AS IL_ProducerID, --report.fnReportGetDealClients(D.DealID) Clients, --report.fnReportGetDealInvestors(D.DealID) Investors, DFM.FinancingMethod, IIF((Dl.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency'), 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorType FROM report.MonthlyDealAllocations D INNER JOIN dbo.Deals Dl ON Dl.DealID = D.DealID INNER JOIN DealTypes DT ON DT.DealTypeID = Dl.DealTypeID INNER JOIN DealPlatforms DP ON DP.PlatformID = DT.PlatformID INNER JOIN DealStages DS ON DS.StageID = D.StageID LEFT JOIN Departments DM ON DM.DepartmentID = ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) LEFT JOIN Departments DX ON DX.DepartmentID = Dl.DepartmentID LEFT JOIN Offices AO ON AO.OfficeID = ISNULL(D.AllocatedOfficeID, Dl.OfficeID) LEFT JOIN Offices DO ON DO.OfficeID = Dl.OfficeID LEFT JOIN dbo.DealProperties DPP ON D.DealID = DPP.DealID AND DPP.IsMainProperty = 1 LEFT JOIN PropertyTypes PT ON PT.PropertyTypeID = DPP.PropertyTypeID LEFT JOIN PropertyTypeGroups PTG ON PTG.GroupID = PT.GroupID LEFT JOIN DealProperties AS DPZ ON D.DealID = DPZ.DealID AND DPZ.isMainProperty = 1 LEFT JOIN Assets AP ON DPZ.AssetID = AP.AssetID LEFT JOIN Cities C ON C.CityID = AP.CityID LEFT JOIN States S ON S.StateID = C.StateID LEFT JOIN Regions R ON ISNULL(C.RCARegionID, ISNULL(S.RCARegionID, S.RegionID)) = R.RegionID LEFT JOIN DealsToClients DTCC ON D.DealID = DTCC.DealID AND DTCC.isPrimary = 1 AND DTCC.MemberType IN (1, 3) LEFT JOIN DealsToClients DTCI ON D.DealID = DTCI.DealID AND DTCI.isPrimary = 1 AND DTCI.MemberType IN (2, 4) LEFT JOIN CacheClients Cl ON DTCC.ClientID = Cl.ClientID LEFT JOIN CacheClients Investor ON DTCI.ClientID = Investor.ClientID LEFT JOIN InvestorTypes ITI ON Investor.InvestorTypeID = ITI.InvestorTypeID LEFT JOIN InvestorTypes ITC ON Cl.InvestorTypeID = ITC.InvestorTypeID --LEFT JOIN (SELECT DISTINCT D2D.DealID, D2D.LinkType FROM DealsToDeals D2D WHERE LinkType = 2) isMultitransactional ON D.DealID = isMultitransactional.DealID LEFT JOIN DealsToEmployees EP ON D.DealID = EP.DealID AND EP.MemberType = 1 AND EP.isPrimary = 1 LEFT JOIN DealsToEmployees EI ON D.DealID = EI.DealID AND EI.MemberType = 2 AND EI.isPrimary = 1 LEFT JOIN Employees P ON EP.EmployeeID = P.EmployeeID LEFT JOIN Employees I ON EI.EmployeeID = I.EmployeeID LEFT JOIN DealFinancingMethods DFM ON Dl.DealFinancingMethodID = DFM.DealFinancingMethodsID LEFT JOIN InvestorTypes AS ITID2C ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID --LEFT JOIN (SELECT DealID, COUNT(QuoteID) AS QtCt FROM Quotes WHERE DateRemoved IS NULL GROUP BY DealID) QCount ON D.dealid = Qcount.DealID go
Below is the index they created.
USE [ABC] GO /****** Object: Index [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID] Script Date: 11/24/2017 11:43:00 PM ******/ CREATE NONCLUSTERED INDEX [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID] ON [report].[MonthlyDealAllocations] ( [DealID] ASC, [AllocatedDepartmentID] ASC, [AllocatedOfficeID] ASC ) INCLUDE ( [PeriodStartDate], [PeriodEndDate], [CountDealCompleted], [StageID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Execution plan link https://www.brentozar.com/pastetheplan/?id=SJu_XK8lM
SQL Script file (Includes schema and index DDL) https://pastebin.com/BB8p9hqa