Sql-Server

加快查詢執行速度。我們可以添加索引或重寫查詢嗎

  • December 1, 2017

我一直在處理一個想要進一步減少查詢執行時間的開發人員交給我的查詢。目前，它需要大約 40 秒並輸出 262K 記錄。根據實際執行，查詢成本更多地用於“CacheClients”上的聚集索引掃描。但是，我不確定添加非聚集覆蓋索引是否會受益。另外，我在計劃的右下角看到了一個表掃描，根據開發人員的說法，他們正在使用該表上的索引進行測試，但他們無法找出適合標準的合適的索引，這基本上是他們將加入 id 欄位並根據日期週期執行查詢。

  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

下面是他們創建的索引。

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

執行計劃連結 https://www.brentozar.com/pastetheplan/?id=SJu_XK8lM

SQL 腳本文件（包括架構和索引 DDL） https://pastebin.com/BB8p9hqa

如果您使用它執行查詢SET STATISTICS IO ON;，將為您提供有關所有連接表所需的 IO 的資訊。根據估計的計劃成本，您將從在CacheClients表格上添加覆蓋索引中獲得最大收益。該表有大約 50 列，但您在查詢中只使用了三列。ClientID在、ClientName和上創建索引InvestorTypeID將減少該表的 IO 要求，因為 SQL Server 將能夠掃描僅包含這三列而不是整個表的索引。即使聯接類型沒有改變，您也會看到好處。

除此之外，這裡沒有很多東西。您的基數估計從MonthlyDealAllocations永遠不會改變，因此您不應該首先加入特定表以提高性能。如果可能，更改一些預期會更改基數的連接LEFT OUTER JOINS或在某些表上定義唯一索引可以改進您的估計，以在整個計劃中將它們保持在常數 262336。這可能對避免節點 id 61 處的 tempdb 溢出有一點好處。

如果您真的很絕望，您可以嘗試通過提示MAXDOP 1或禁用來查看查詢執行時會發生什麼。正如您從一堆不同的表中請求數據一樣，SQL Server 將花費時間來完成所有這些連接。沒有神奇的查詢重寫會改變這一點。我建議通過定義一個可接受的最小查詢性能時間目標來解決這個問題，而不是只想在沒有具體目標的情況下讓它更快。MERGE JOINS``OPTION (LOOP JOIN, HASH JOIN)

您的查詢簡單而冗長。

i）您需要在查詢中進行較小的更正。希望您的查詢給出正確的輸出。也希望所有其他左連接都是合理的。

LEFT JOIN Departments DM
     ON DM.DepartmentID = ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID)
   LEFT JOIN Departments DX
     ON DX.DepartmentID = Dl.DepartmentID

這可以寫成，

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

改寫為，

LEFT JOIN Offices AO
     ON AO.OfficeID = D.AllocatedOfficeID
   LEFT JOIN Offices DO
     ON DO.OfficeID = Dl.OfficeID

將此行替換為，

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 :我對 MAXDOP 了解不多。您目前的 MAXDOP 為 4。如果通過查詢 HINT 減少到 MAXDOP=2，那麼速度可能會有所提高。同時我不確定。

Index :恕我直言，您應該在 MonthlyDealAllocations 上刪除現有的複合 NON CI，因為複合索引不能以這種方式工作。為每個單獨創建 NON CI 並保留一個覆蓋索引。為什麼 MonthlyDealAllocations 中沒有 CI。如果有任何 CI 並且它我們正在使用然後不需要覆蓋索引。

每個表上也應該有超過 400 行的索引。我不確定數字。

引用自：https://dba.stackexchange.com/questions/191696

相關問答

Sql-Server

如何優化：COUNT(*)>1

  • January 15, 2015
檢視問答
Sql-Server

在選擇具有許多“分組依據”列的幾乎所有行時提高查詢性能

  • June 22, 2014
檢視問答
Sql-Server

如何從查詢中消除這種代價高昂的索引查找操作？

  • August 16, 2021
檢視問答
Sql-Server

如何提高查詢性能？

  • May 24, 2021
檢視問答
Sql-Server

開發人員的查詢在自定義數據庫中表現很差

  • December 3, 2020
檢視問答
Sql-Server

隨時間推移預測查詢性能的合理方法

  • November 16, 2020
檢視問答