加快查詢速度的方法

我的查詢返回大約 590 行和 8 列。我遇到的問題是，從開始到結束查詢需要 2 分 30 秒才能完成。這裡偉大的一群人教會了我很多如何編寫更有效的查詢，所以這裡是另一個！

我使用的是date變數而不是 a，datetime因為我的變數只包含一個日期 - 而且我還使用Aaron Bertrand - Bad Habits To Kickyyyymmdd建議的格式儲存我的日期。

我能做些什麼來優化這個查詢並讓結果更快返回嗎？

DECLARE @Startdate date = '20170101', 
       @Enddate   date = '20170131';

WITH fc As
(
   Select
   Teacher
   ,Team
   ,fc
   FROM [Helper].[dbo].[fc]
)
,ia As
(
   Select
   Teacher
   ,tia
   FROM dbo.ia
   WHERE [hiredate] >= @Startdate
   AND [hiredate] <  DATEADD(DAY,1,@Enddate)
)
,inb As
(
   Select 
   Teacher
   ,tinb
   FROM inb
),
ripcord As
(
   Select
   Teacher
   ,pit
   FROM [homebase].[dbo].[rip]
)
,YRTR As
(
   Select
   Teacher
   ,totamt
   FROM totamt
   WHERE CAST([begindate] As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date) 
                                    AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
Select 
DISTINCT rost.[Teacher] As Teacher
,[Team Name] = fc.team
,[TIA] = ROUND(SUM(ISNULL(ia.tia,0)),0)
,[SUM] = CAST(ROUND(SUM(ISNULL(inb.tinb,0))+SUM(ISNULL(rip.pit,0)),0) As INT)
,[YR] = ROUND(SUM(ISNULL(tr.totamt,0)),0)
,[fc] = ISNULL(fc,0)
,[1st Count] = COALESCE(b.[students],0)
,[2nd Count] = COALESCE(c.[potstudents],0)
FROM dbo.roster rost
LEFT JOIN fc fc
ON rost.Teacher = fc.Teacher
LEFT JOIN ia ia
ON ia.Teacher = rost.Teacher
LEFT JOIN inb inb
ON rost.Teacher = inb.Teacher
LEFT JOIN ripcord rip
ON rost.Teacher = rip.Teacher
LEFT JOIN YRTR tr
ON rost.Teacher = tr.Teacher
OUTER APPLY
( SELECT 
       DISTINCT Teacher [Teacher]
       , COUNT(students) AS students
   FROM students
   WHERE Teacher = rost.Teacher
   AND regdate >= @Startdate
   AND regdate <  DATEADD(DAY,1,@Enddate)
   GROUP BY Teacher
) AS b
OUTER APPLY
(
   Select
   DISTINCT Teacher Teacher
   ,COUNT(potstudents) As potstudents
   FROM dbo.potstudents
   WHERE Teacher = rost.Teacher
   AND returndate >= @Startdate
   AND returndate <  DATEADD(DAY,1,@Enddate)
   GROUP BY Teacher
) AS c
GROUP BY rost.[Teacher],fc.TEAM, fc.fc,b.[students],c.[potstudents]
ORDER BY rost.[Teacher] ASC

編輯
（忘記連結，抱歉）在@sabin bio 的要求下 - 這是查詢執行計劃的連結

此外，CTE 的查詢視圖上沒有索引。

首先了解查詢優化器由您的查詢生成的計劃。在您的問題中，您說查詢返回大約 590 行，但包含的查詢計劃僅返回 18 行。你附上正確的計劃了嗎？無論如何，我會走過它。從右到左閱讀：

進行全表掃描roster並取回 18 行。此結果集用作以下所有嵌套循環連接的外部部分。對於外部結果集中的每一行：

• 對ia桌子進行全面掃描
• 對inb桌子進行全面掃描
• 對ripcord桌子進行全面掃描
• 對YRTR桌子進行全面掃描
• 對students桌子進行全面掃描
• 對potstudents桌子進行全面掃描

在某些時候，行數會增加到 19。GROUP BY被實現為SORT節點 id 為 4 的 。DISTINCT被實現為Sort (Distinct Sort)節點 id 為 0 的 a。

作為程序員，您無法直接控制查詢計劃，但您可以做很多事情來影響它。你說查詢執行時間太慢了。上述計劃對您來說是否有效？如果您可以選擇查詢優化器的操作，您會這樣做嗎？您正在進行大約 140 次表掃描以返回 18 行。對於極少數行，這可能沒問題，但聽起來您在生產中有更多數據。

優化查詢的一種方法是減少該查詢的 IO 要求。在這裡，您正在執行大量表掃描，因此應該很容易實現。創建索引，以便查詢優化器可以更有效地獲取相關數據。即使您正在引用視圖，您也可以在視圖使用的表上創建索引以提高性能。舉個例子，這裡是表訪問的謂詞 on fc：

[Test].[dbo].[roster].[Teacher] as [rost].[Teacher]=[Test].[dbo].[fc].[Teacher]

Teacher如果您在其列上創建索引，則fc可能會導致該表的表訪問方法不同，並且可能更有效。或者，您可以將教師表使用的所有列添加為INCLUDE列。

僅查看您的程式碼，您應該意識到在查詢中使用局部變數的影響。查詢優化器在創建查詢計劃時不會知道這些局部變數的值。它將根據硬編碼規則進行預設基數估計。對於某些查詢，添加OPTION (RECOMPILE)提示或用硬編碼值替換局部變數可以產生更好的查詢計劃，因為查詢優化器在創建計劃之前就知道變數的值。

此外，不要隨意添加DISTINCT和GROUP BY到您的查詢。看起來查詢優化器優化了其中的一些，但大多數情況下，如果你同時擁有這兩者GROUP BY並且DISTINCT在同一個查詢中你做錯了什麼。目前尚不清楚進行該更改對該查詢有多大的性能影響。

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