Question :
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
Answer :
If you run your query with SET STATISTICS IO ON; that’ll give you information about IO required for all of your joined tables. Based on estimated plan costs you’ll get the most benefit from adding a covering index on the CacheClients table. That table has around 50 columns but you only use three columns in your query. Creating an index on ClientID, ClientName, and InvestorTypeID will reduce IO requirements for that table because SQL Server will be able to scan the index that contains only those three columns instead of the full table. You will see benefits even if the join type doesn’t change.
Other than that there’s not a lot going here. Your cardinality estimate starting at MonthlyDealAllocations never changes, so it’s not like you should be joining to a specific table first to improve performance. If possible, changing some of the joins expected to change cardinality to LEFT OUTER JOINS or defining unique indexes on some of the tables could improve your estimates to keep them at a constant 262336 throughout the plan. This could have a minor benefit in avoiding the tempdb spill at node id 61.
If you’re really desperate you could try a MAXDOP 1 hint or disabling MERGE JOINS via OPTION (LOOP JOIN, HASH JOIN) just to see what happens to the query’s runtime. As is you’re asking for data from a bunch of different tables and SQL Server is going to take time to do all of those joins. There’s no magic query rewrite that’s going to change that. I recommend approaching this problem by defining a minimum acceptable query performance time target instead of just wanting to make it faster without a concrete goal in mind.
Your Query is simple and lengthy.
i) you need minor correction in your query.Hope your query is giving correct output.Also hope all other left join are justified.
LEFT JOIN Departments DM
ON DM.DepartmentID = ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID)
LEFT JOIN Departments DX
ON DX.DepartmentID = Dl.DepartmentID
This can be written as,
LEFT JOIN Departments DM
ON DM.DepartmentID = D.AllocatedDepartmentID
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
Rewrite it as,
LEFT JOIN Offices AO
ON AO.OfficeID = D.AllocatedOfficeID
LEFT JOIN Offices DO
ON DO.OfficeID = Dl.OfficeID
Replace this line with,
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 Cities C
ON C.CityID = AP.CityID
LEFT JOIN States S
ON S.StateID = C.StateID
LEFT JOIN Regions R
ON R.RegionID=C.RCARegionID
LEFT JOIN Regions R1
R1.RegionID=(S.RCARegionID and S.RCARegionID is not NULL) or (S.RegionID and S.RCARegionID is NULL))
MAXDOP : I do not have much knowledge on MAXDOP.your current MAXDOP is 4.if it is reduce to MAXDOP=2 through query HINT then there might be boost in speed.I am not sure at the same time.
Index : IMHO,you should drop your existing composite NON CI on MonthlyDealAllocations,becasue composite index do not work in this manner.Create seperate NON CI for each and keep one covering index.Why there is no CI in MonthlyDealAllocations .if there is any CI and it us being use then no need of covering index.
Also there should be index on each table which has more than 400 rows.I am not sure about number.