November 24, 2017 at 10:57 pm
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.
I've attached the query for reference. Please do let me know if there is anything else needed and I would be glad to provide the necessary information.
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 that they added.
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
November 24, 2017 at 11:27 pm
Quick question, can you run the query with OPTION (MAXDOP 1) and post the actual execution plan? The paralellism is causing lots of noise and my thought is that it's doing more harm than good.
😎
Could you also post the DDL (create table & indices) scripts, makes it easier to analyse the query and the execution plan 😉
November 25, 2017 at 6:43 am
All these functions on your join criteria like this one are going to prevent index use and slow down performance:ISNULL((DD..AllocatedDepartmentIDAllocatedDepartmentID,, DlDl..DepartmentIDDepartmentID))
Every one of those will lead to table or index scans preventing any good index use. Also, the fact that you're not filtering the data at all with any kind of WHERE clause means you're going to moving all of some of the data, again, scans. Tuning to move all the data out of a set of tables like this is much more about hardware than indexing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2017 at 2:57 pm
Grant Fritchey - Saturday, November 25, 2017 6:43 AMAll these functions on your join criteria like this one are going to prevent index use and slow down performance:ISNULL((DD..AllocatedDepartmentIDAllocatedDepartmentID,, DlDl..DepartmentIDDepartmentID))
Every one of those will lead to table or index scans preventing any good index use. Also, the fact that you're not filtering the data at all with any kind of WHERE clause means you're going to moving all of some of the data, again, scans. Tuning to move all the data out of a set of tables like this is much more about hardware than indexing.
Thanks! Grant. Could you please provide a way to get rid of these ISNULL functions or try to use it in a manner so that at least proper indexes can be put to good use. I know this stuff is a bit more complex in the way it is written.
November 25, 2017 at 3:55 pm
Eirikur Eiriksson - Friday, November 24, 2017 11:27 PMQuick question, can you run the query with OPTION (MAXDOP 1) and post the actual execution plan? The paralellism is causing lots of noise and my thought is that it's doing more harm than good.
😎
Could you also post the DDL (create table & indices) scripts, makes it easier to analyse the query and the execution plan 😉
I will do it right away. I tried doing it with option (maxdop 1) it is taking around 20 to 24 secs but this time is also in accordance to the time it takes if I add the index below and let it run in parallel.
CREATE NONCLUSTERED INDEX [IX_CacheClients_ClientID_InvestorTypeID] ON [dbo].[CacheClients]
(
[ClientID] ASC,
[InvestorTypeID] ASC
)
INCLUDE ([ClientName]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
November 26, 2017 at 2:46 am
Here is a quick suggestion as a starting point, (probably better to keep the maxdop 1)
😎
;WITH BASE_DATA AS
(
SELECT
D.PeriodStartDate
,D.PeriodEndDate
,D.DealID
,Dl.DealName
,D.StageID
,D.OfficeAllocVolumePipeline
,D.OfficeAllocVolumeCompleted
,D.OfficeAllocFeePipeline
,D.OfficeAllocFeeCompleted
,D.OfficeAllocVolumePipelineGBP
,D.OfficeAllocVolumeCompletedGBP
,D.OfficeAllocFeePipelineGBP
,D.OfficeAllocFeeCompletedGBP
,D.CountDealCompleted
,ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID
,ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID
,Dl.OfficeID
,Dl.DepartmentID
,Dl.isSecuritized
,Dl.isServiced
,Dl.PortfolioID
,Dl.DealFinancingMethodID
,DT.DealType
,DP.DisplayWithOffice
,DP.ShortName
,DP.PlatformID
,DP.Platform
,DS.Stage
FROM report.MonthlyDealAllocations D
INNER JOIN dbo.Deals Dl
ON Dl.DealID = D.DealID
INNER JOIN dbo.DealTypes DT
ON DT.DealTypeID = Dl.DealTypeID
INNER JOIN dbo.DealPlatforms DP
ON DP.PlatformID = DT.PlatformID
INNER JOIN dbo.DealStages DS
ON DS.StageID = D.StageID
)
SELECT
BD.PeriodStartDate
,BD.PeriodEndDate
,BD.DealID
,BD.DealName
,BD.StageID
,CONVERT(varchar(1), BD.StageID) + ' - ' + BD.Stage Stage
,BD.Platform
,BD.AllocatedOfficeID
,BD.AllocatedDepartmentID
,CASE
WHEN BD.AllocatedDepartmentID = 1 THEN 'Investment Advisory'
WHEN BD.AllocatedDepartmentID = 2 THEN 'Debt/EP'
WHEN BD.AllocatedDepartmentID = 6 THEN 'HFF Securities'
ELSE DM.Department
END AS AllocatedDepartment
,AO.Name AllocatedOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN AO.Name + ' ' + BD.ShortName
ELSE AO.Name
END AS AllocatedOfficeDept
,BD.DepartmentID DealDepartmentID
,DX.Department DealDepartment
,BD.OfficeID DealOfficeID
,DO.Name DealOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN DO.Name + ' ' + BD.ShortName
ELSE DO.Name
END DealOfficeDept
,BD.DealType
,BD.OfficeAllocVolumePipeline
,BD.OfficeAllocVolumeCompleted
,BD.OfficeAllocFeePipeline
,BD.OfficeAllocFeeCompleted
,BD.OfficeAllocVolumePipelineGBP
,BD.OfficeAllocVolumeCompletedGBP
,BD.OfficeAllocFeePipelineGBP
,BD.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
,BD.CountDealCompleted
,CASE BD.isSecuritized
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isSecuritized
,CASE BD.isServiced
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isServiced
,SIGN(BD.PortfolioID) AS isPortfolio
,BD.PlatformID
,P.EmployeeID AS PrimaryProducerID
,I.EmployeeID AS IL_ProducerID
,DFM.FinancingMethod
,IIF((BD.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency')
, 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorType
FROM BASE_DATA BD
LEFT OUTER JOIN dbo.Departments DM
ON BD.AllocatedDepartmentID = DM.DepartmentID
LEFT OUTER JOIN dbo.Departments DX
ON BD.DepartmentID = DX.DepartmentID
LEFT OUTER JOIN dbo.Offices AO
ON BD.AllocatedOfficeID = AO.OfficeID
LEFT OUTER JOIN dbo.Offices DO
ON BD.OfficeID = DO.OfficeID
LEFT OUTER JOIN dbo.DealProperties DPP
ON BD.DealID = DPP.DealID
AND DPP.IsMainProperty = 1
LEFT OUTER JOIN dbo.PropertyTypes PT
ON PT.PropertyTypeID = DPP.PropertyTypeID
LEFT OUTER JOIN dbo.PropertyTypeGroups PTG
ON PTG.GroupID = PT.GroupID
LEFT OUTER JOIN dbo.DealFinancingMethods DFM
ON BD.DealFinancingMethodID = DFM.DealFinancingMethodsID
LEFT OUTER JOIN dbo.DealProperties DPZ
ON BD.DealID = DPZ.DealID
AND DPZ.isMainProperty = 1
LEFT OUTER JOIN dbo.Assets AP
ON DPZ.AssetID = AP.AssetID
LEFT OUTER JOIN dbo.Cities C
ON C.CityID = AP.CityID
LEFT OUTER JOIN dbo.States S
ON S.StateID = C.StateID
LEFT OUTER JOIN dbo.Regions R
ON R.RegionID = COALESCE(C.RCARegionID, S.RCARegionID, S.RegionID)
LEFT OUTER JOIN dbo.DealsToClients DTCC
ON BD.DealID = DTCC.DealID
AND DTCC.isPrimary = 1
AND DTCC.MemberType IN (1, 3)
LEFT OUTER JOIN dbo.DealsToClients DTCI
ON BD.DealID = DTCI.DealID
AND DTCI.isPrimary = 1
AND DTCI.MemberType IN (2, 4)
LEFT OUTER JOIN dbo.CacheClients Cl
ON DTCC.ClientID = Cl.ClientID
LEFT OUTER JOIN dbo.CacheClients Investor
ON DTCI.ClientID = Investor.ClientID
LEFT OUTER JOIN dbo.InvestorTypes ITI
ON Investor.InvestorTypeID = ITI.InvestorTypeID
LEFT OUTER JOIN dbo.InvestorTypes ITC
ON Cl.InvestorTypeID = ITC.InvestorTypeID
LEFT OUTER JOIN dbo.DealsToEmployees EP
ON BD.DealID = EP.DealID
AND EP.MemberType = 1
AND EP.isPrimary = 1
LEFT OUTER JOIN dbo.DealsToEmployees EI
ON BD.DealID = EI.DealID
AND EI.MemberType = 2
AND EI.isPrimary = 1
LEFT OUTER JOIN dbo.Employees P
ON EP.EmployeeID = P.EmployeeID
LEFT OUTER JOIN dbo.Employees I
ON EI.EmployeeID = I.EmployeeID
LEFT OUTER JOIN dbo.InvestorTypes ITID2C
ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID
OPTION (MAXDOP 1)
;
November 26, 2017 at 2:07 pm
ffarouqi - Saturday, November 25, 2017 2:57 PMGrant Fritchey - Saturday, November 25, 2017 6:43 AMAll these functions on your join criteria like this one are going to prevent index use and slow down performance:ISNULL((DD..AllocatedDepartmentIDAllocatedDepartmentID,, DlDl..DepartmentIDDepartmentID))
Every one of those will lead to table or index scans preventing any good index use. Also, the fact that you're not filtering the data at all with any kind of WHERE clause means you're going to moving all of some of the data, again, scans. Tuning to move all the data out of a set of tables like this is much more about hardware than indexing.Thanks! Grant. Could you please provide a way to get rid of these ISNULL functions or try to use it in a manner so that at least proper indexes can be put to good use. I know this stuff is a bit more complex in the way it is written.
It probably requires restructuring the database. You're taking the first column that is null, but that means a calculation against the columns. If it was just about "This OR That" you could use the OR clause. However this is about having to run that calculation against each value in the table to find the ones that match. I can't immediately answer the question of what to do to fix this because I don't know what the data structure and data are that are forcing you to make this choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2017 at 2:15 pm
Eirikur Eiriksson - Sunday, November 26, 2017 2:46 AMHere is a quick suggestion as a starting point, (probably better to keep the maxdop 1)
😎
;WITH BASE_DATA AS
(
SELECT
D.PeriodStartDate
,D.PeriodEndDate
,D.DealID
,Dl.DealName
,D.StageID
,D.OfficeAllocVolumePipeline
,D.OfficeAllocVolumeCompleted
,D.OfficeAllocFeePipeline
,D.OfficeAllocFeeCompleted
,D.OfficeAllocVolumePipelineGBP
,D.OfficeAllocVolumeCompletedGBP
,D.OfficeAllocFeePipelineGBP
,D.OfficeAllocFeeCompletedGBP
,D.CountDealCompleted
,ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID
,ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID
,Dl.OfficeID
,Dl.DepartmentID
,Dl.isSecuritized
,Dl.isServiced
,Dl.PortfolioID
,Dl.DealFinancingMethodID
,DT.DealType
,DP.DisplayWithOffice
,DP.ShortName
,DP.PlatformID
,DP.Platform
,DS.Stage
FROM report.MonthlyDealAllocations D
INNER JOIN dbo.Deals Dl
ON Dl.DealID = D.DealID
INNER JOIN dbo.DealTypes DT
ON DT.DealTypeID = Dl.DealTypeID
INNER JOIN dbo.DealPlatforms DP
ON DP.PlatformID = DT.PlatformID
INNER JOIN dbo.DealStages DS
ON DS.StageID = D.StageID
)
SELECT
BD.PeriodStartDate
,BD.PeriodEndDate
,BD.DealID
,BD.DealName
,BD.StageID
,CONVERT(varchar(1), BD.StageID) + ' - ' + BD.Stage Stage
,BD.Platform
,BD.AllocatedOfficeID
,BD.AllocatedDepartmentID
,CASE
WHEN BD.AllocatedDepartmentID = 1 THEN 'Investment Advisory'
WHEN BD.AllocatedDepartmentID = 2 THEN 'Debt/EP'
WHEN BD.AllocatedDepartmentID = 6 THEN 'HFF Securities'
ELSE DM.Department
END AS AllocatedDepartment
,AO.Name AllocatedOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN AO.Name + ' ' + BD.ShortName
ELSE AO.Name
END AS AllocatedOfficeDept
,BD.DepartmentID DealDepartmentID
,DX.Department DealDepartment
,BD.OfficeID DealOfficeID
,DO.Name DealOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN DO.Name + ' ' + BD.ShortName
ELSE DO.Name
END DealOfficeDept
,BD.DealType
,BD.OfficeAllocVolumePipeline
,BD.OfficeAllocVolumeCompleted
,BD.OfficeAllocFeePipeline
,BD.OfficeAllocFeeCompleted
,BD.OfficeAllocVolumePipelineGBP
,BD.OfficeAllocVolumeCompletedGBP
,BD.OfficeAllocFeePipelineGBP
,BD.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
,BD.CountDealCompleted
,CASE BD.isSecuritized
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isSecuritized
,CASE BD.isServiced
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isServiced
,SIGN(BD.PortfolioID) AS isPortfolio
,BD.PlatformID
,P.EmployeeID AS PrimaryProducerID
,I.EmployeeID AS IL_ProducerID
,DFM.FinancingMethod
,IIF((BD.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency')
, 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorTypeFROM BASE_DATA BD
LEFT OUTER JOIN dbo.Departments DM
ON BD.AllocatedDepartmentID = DM.DepartmentID
LEFT OUTER JOIN dbo.Departments DX
ON BD.DepartmentID = DX.DepartmentID
LEFT OUTER JOIN dbo.Offices AO
ON BD.AllocatedOfficeID = AO.OfficeID
LEFT OUTER JOIN dbo.Offices DO
ON BD.OfficeID = DO.OfficeID
LEFT OUTER JOIN dbo.DealProperties DPP
ON BD.DealID = DPP.DealID
AND DPP.IsMainProperty = 1
LEFT OUTER JOIN dbo.PropertyTypes PT
ON PT.PropertyTypeID = DPP.PropertyTypeID
LEFT OUTER JOIN dbo.PropertyTypeGroups PTG
ON PTG.GroupID = PT.GroupID
LEFT OUTER JOIN dbo.DealFinancingMethods DFM
ON BD.DealFinancingMethodID = DFM.DealFinancingMethodsID
LEFT OUTER JOIN dbo.DealProperties DPZ
ON BD.DealID = DPZ.DealID
AND DPZ.isMainProperty = 1
LEFT OUTER JOIN dbo.Assets AP
ON DPZ.AssetID = AP.AssetID
LEFT OUTER JOIN dbo.Cities C
ON C.CityID = AP.CityID
LEFT OUTER JOIN dbo.States S
ON S.StateID = C.StateID
LEFT OUTER JOIN dbo.Regions R
ON R.RegionID = COALESCE(C.RCARegionID, S.RCARegionID, S.RegionID)
LEFT OUTER JOIN dbo.DealsToClients DTCC
ON BD.DealID = DTCC.DealID
AND DTCC.isPrimary = 1
AND DTCC.MemberType IN (1, 3)
LEFT OUTER JOIN dbo.DealsToClients DTCI
ON BD.DealID = DTCI.DealID
AND DTCI.isPrimary = 1
AND DTCI.MemberType IN (2, 4)
LEFT OUTER JOIN dbo.CacheClients Cl
ON DTCC.ClientID = Cl.ClientID
LEFT OUTER JOIN dbo.CacheClients Investor
ON DTCI.ClientID = Investor.ClientID
LEFT OUTER JOIN dbo.InvestorTypes ITI
ON Investor.InvestorTypeID = ITI.InvestorTypeID
LEFT OUTER JOIN dbo.InvestorTypes ITC
ON Cl.InvestorTypeID = ITC.InvestorTypeID
LEFT OUTER JOIN dbo.DealsToEmployees EP
ON BD.DealID = EP.DealID
AND EP.MemberType = 1
AND EP.isPrimary = 1
LEFT OUTER JOIN dbo.DealsToEmployees EI
ON BD.DealID = EI.DealID
AND EI.MemberType = 2
AND EI.isPrimary = 1
LEFT OUTER JOIN dbo.Employees P
ON EP.EmployeeID = P.EmployeeID
LEFT OUTER JOIN dbo.Employees I
ON EI.EmployeeID = I.EmployeeID
LEFT OUTER JOIN dbo.InvestorTypes ITID2C
ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID
OPTION (MAXDOP 1)
;
Thanks! but it didn't help any better...it is still in the same execution range.
November 29, 2017 at 11:46 pm
The query returns every single record from the "Allocations" table joined to every single relevant record in lookup tables.
Since you've got no record filtering, it may be only table (clustered index) scan and hash join.
not much can be done about it.
The only issue I can spot in the execution plan is here:
LEFT OUTER JOIN dbo.Employees P
ON EP.EmployeeID = P.EmployeeID
This join brings the number of records from 262k to 365k, which means - there are either duplicate EmployeeID's in
dbo.DealsToEmployees EP or multiple EmployeeID's are assigned to the same deal (assuming EmployeeID is a PK in dbo.Employees)
_____________
Code for TallyGenerator
November 30, 2017 at 3:58 am
ffarouqi - Sunday, November 26, 2017 2:15 PMEirikur Eiriksson - Sunday, November 26, 2017 2:46 AMHere is a quick suggestion as a starting point, (probably better to keep the maxdop 1)
😎
;WITH BASE_DATA AS
(
SELECT
D.PeriodStartDate
,D.PeriodEndDate
,D.DealID
,Dl.DealName
,D.StageID
,D.OfficeAllocVolumePipeline
,D.OfficeAllocVolumeCompleted
,D.OfficeAllocFeePipeline
,D.OfficeAllocFeeCompleted
,D.OfficeAllocVolumePipelineGBP
,D.OfficeAllocVolumeCompletedGBP
,D.OfficeAllocFeePipelineGBP
,D.OfficeAllocFeeCompletedGBP
,D.CountDealCompleted
,ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID
,ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID
,Dl.OfficeID
,Dl.DepartmentID
,Dl.isSecuritized
,Dl.isServiced
,Dl.PortfolioID
,Dl.DealFinancingMethodID
,DT.DealType
,DP.DisplayWithOffice
,DP.ShortName
,DP.PlatformID
,DP.Platform
,DS.Stage
FROM report.MonthlyDealAllocations D
INNER JOIN dbo.Deals Dl
ON Dl.DealID = D.DealID
INNER JOIN dbo.DealTypes DT
ON DT.DealTypeID = Dl.DealTypeID
INNER JOIN dbo.DealPlatforms DP
ON DP.PlatformID = DT.PlatformID
INNER JOIN dbo.DealStages DS
ON DS.StageID = D.StageID
)
SELECT
BD.PeriodStartDate
,BD.PeriodEndDate
,BD.DealID
,BD.DealName
,BD.StageID
,CONVERT(varchar(1), BD.StageID) + ' - ' + BD.Stage Stage
,BD.Platform
,BD.AllocatedOfficeID
,BD.AllocatedDepartmentID
,CASE
WHEN BD.AllocatedDepartmentID = 1 THEN 'Investment Advisory'
WHEN BD.AllocatedDepartmentID = 2 THEN 'Debt/EP'
WHEN BD.AllocatedDepartmentID = 6 THEN 'HFF Securities'
ELSE DM.Department
END AS AllocatedDepartment
,AO.Name AllocatedOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN AO.Name + ' ' + BD.ShortName
ELSE AO.Name
END AS AllocatedOfficeDept
,BD.DepartmentID DealDepartmentID
,DX.Department DealDepartment
,BD.OfficeID DealOfficeID
,DO.Name DealOffice
,CASE
WHEN BD.DisplayWithOffice = 1 THEN DO.Name + ' ' + BD.ShortName
ELSE DO.Name
END DealOfficeDept
,BD.DealType
,BD.OfficeAllocVolumePipeline
,BD.OfficeAllocVolumeCompleted
,BD.OfficeAllocFeePipeline
,BD.OfficeAllocFeeCompleted
,BD.OfficeAllocVolumePipelineGBP
,BD.OfficeAllocVolumeCompletedGBP
,BD.OfficeAllocFeePipelineGBP
,BD.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
,BD.CountDealCompleted
,CASE BD.isSecuritized
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isSecuritized
,CASE BD.isServiced
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE ''
END AS isServiced
,SIGN(BD.PortfolioID) AS isPortfolio
,BD.PlatformID
,P.EmployeeID AS PrimaryProducerID
,I.EmployeeID AS IL_ProducerID
,DFM.FinancingMethod
,IIF((BD.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency')
, 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorTypeFROM BASE_DATA BD
LEFT OUTER JOIN dbo.Departments DM
ON BD.AllocatedDepartmentID = DM.DepartmentID
LEFT OUTER JOIN dbo.Departments DX
ON BD.DepartmentID = DX.DepartmentID
LEFT OUTER JOIN dbo.Offices AO
ON BD.AllocatedOfficeID = AO.OfficeID
LEFT OUTER JOIN dbo.Offices DO
ON BD.OfficeID = DO.OfficeID
LEFT OUTER JOIN dbo.DealProperties DPP
ON BD.DealID = DPP.DealID
AND DPP.IsMainProperty = 1
LEFT OUTER JOIN dbo.PropertyTypes PT
ON PT.PropertyTypeID = DPP.PropertyTypeID
LEFT OUTER JOIN dbo.PropertyTypeGroups PTG
ON PTG.GroupID = PT.GroupID
LEFT OUTER JOIN dbo.DealFinancingMethods DFM
ON BD.DealFinancingMethodID = DFM.DealFinancingMethodsID
LEFT OUTER JOIN dbo.DealProperties DPZ
ON BD.DealID = DPZ.DealID
AND DPZ.isMainProperty = 1
LEFT OUTER JOIN dbo.Assets AP
ON DPZ.AssetID = AP.AssetID
LEFT OUTER JOIN dbo.Cities C
ON C.CityID = AP.CityID
LEFT OUTER JOIN dbo.States S
ON S.StateID = C.StateID
LEFT OUTER JOIN dbo.Regions R
ON R.RegionID = COALESCE(C.RCARegionID, S.RCARegionID, S.RegionID)
LEFT OUTER JOIN dbo.DealsToClients DTCC
ON BD.DealID = DTCC.DealID
AND DTCC.isPrimary = 1
AND DTCC.MemberType IN (1, 3)
LEFT OUTER JOIN dbo.DealsToClients DTCI
ON BD.DealID = DTCI.DealID
AND DTCI.isPrimary = 1
AND DTCI.MemberType IN (2, 4)
LEFT OUTER JOIN dbo.CacheClients Cl
ON DTCC.ClientID = Cl.ClientID
LEFT OUTER JOIN dbo.CacheClients Investor
ON DTCI.ClientID = Investor.ClientID
LEFT OUTER JOIN dbo.InvestorTypes ITI
ON Investor.InvestorTypeID = ITI.InvestorTypeID
LEFT OUTER JOIN dbo.InvestorTypes ITC
ON Cl.InvestorTypeID = ITC.InvestorTypeID
LEFT OUTER JOIN dbo.DealsToEmployees EP
ON BD.DealID = EP.DealID
AND EP.MemberType = 1
AND EP.isPrimary = 1
LEFT OUTER JOIN dbo.DealsToEmployees EI
ON BD.DealID = EI.DealID
AND EI.MemberType = 2
AND EI.isPrimary = 1
LEFT OUTER JOIN dbo.Employees P
ON EP.EmployeeID = P.EmployeeID
LEFT OUTER JOIN dbo.Employees I
ON EI.EmployeeID = I.EmployeeID
LEFT OUTER JOIN dbo.InvestorTypes ITID2C
ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID
OPTION (MAXDOP 1)
;Thanks! but it didn't help any better...it is still in the same execution range.
As I said earlier, this is a starting point, post the actual execution plan for this query and we can take it from there.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply