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

我有一個有 20 列和大約 600,000 條記錄的表。最大行大小僅為 100 字節左右。該表每隔幾天就會重新填充一次，但記錄數保持大致相同。

目前只有一個聚集索引：主鍵的 int 標識列。

我有幾個依賴於這個表的查詢和視圖，通常需要 5-10 秒才能執行。當我簡單地選擇所有記錄 ( select * from myTable) 時，檢索所有結果大約需要 4 秒。

我一直無法找到在 SQL Server 中選擇 500,000 條記錄的相關基準。這個時間是典型的嗎？

這是我在表上執行的典型查詢：

select  CO.Company
   ,CO.Location
   ,CO.Account
   ,CO.SalesRoute
   ,CO.Employee
   ,CO.ProductType
   ,CO.Item
   ,CO.LoadJDate
   ,CO.CommissionRate
   ,SUM(CO.[Extended Sales Price]) AS Sales_Dollars
   ,SUM(CO.[Delivered Qty]) AS Quantity
from    dbo.Commissions_Output CO
where   CO.[Extended Sales Price] <> 0
group by    CO.Company
       ,CO.Location
       ,CO.Account
       ,CO.SalesRoute
       ,CO.Employee
       ,CO.ProductType
       ,CO.Item
       ,CO.LoadJDate
       ,CO.CommissionRate

當我在表上至少有一個非聚集索引時，我得到以下結果：

掃描計數 18，邏輯讀取 18372；CPU 時間 = 24818 毫秒，經過時間 = 8614 毫秒。

我嘗試了各種索引和組合（過濾列上的索引，包括分組列；所有過濾/分組列上的索引並包括聚合列；等等）。它們都提供相同的性能，並且幾乎總是使用相同的執行計劃。

當我刪除除聚集索引 (PK) 之外的所有內容時，性能通常會提高 3-4 秒。當掃描計數減半時，邏輯讀取減少。

關於數據的一些注意事項：分組前的select和where子句的結果大約有500,000行（幾乎是整個表）。通過分組僅組合了大約 10,000 行，在分組後仍然留下大約 500,000 條記錄。

沒有非聚集索引的執行計劃顯示成本最高的操作是雜湊匹配 (49%) 和 where 子句的聚集索引掃描 (35%)。MSSMS 建議我為[Extended Sales Price]. 具有至少一個非聚集索引的執行計劃顯示成本最高的操作是排序（在分組依據列上）。

鑑於此查詢返回幾乎所有記錄並且 group-by 幾乎不會減少行數，**這是否與查詢一樣快？**看起來很慢，我閱讀了有關人們在 1000 毫秒內返回數十萬行的文章和 SO 問題。我錯過了什麼，或者這是一個相當典型的速度？規範化此表目前不是一種選擇，我不確定這會有多大幫助。

最後一點：我有幾個視圖和其他涉及加入該表的查詢（有一些規範化）。起初我認為這些視圖和查詢很慢，因為連接錯誤等等，但看起來真正的罪魁禍首是這個表和它的初始查詢。大多數查詢和視圖都適用於表中的幾乎所有數據。當我選擇單列或一小部分行時，執行時間很好，但這很少見。

**更新：**這裡是所有執行時間、計劃和 IO 統計資訊。我沒有執行每個查詢數百次，但執行時間似乎沒有超過 1000 毫秒的“熱”與“冷”差異。

沒有非聚集索引，沒有 MAXDOP 設置：

表’Commissions_Output’。掃描計數 9，邏輯讀取 11263，物理讀取 0，預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

CPU 時間 = 6690 毫秒，經過時間 = 4605 毫秒。（最大 CPU 時間 = 7516 毫秒，最小經過時間 = 3754 毫秒。）

使用非聚集索引，無 MAXDOP 設置：

表’Commissions_Output’。掃描計數 16，邏輯讀取 6227

CPU 時間 = 6591 毫秒，經過時間 = 3717 毫秒。

沒有非聚集索引，MAXDOP 1：

表’Commissions_Output’。掃描計數 1，邏輯讀取 10278

CPU 時間 = 2656 毫秒，經過時間 = 4991 毫秒。

使用非聚集索引，MAXDOP 1：

表’Commissions_Output’。掃描計數 1，邏輯讀取 10278

CPU 時間 = 2656 毫秒，經過時間 = 4991 毫秒。

使用的非聚集索引：

create nonclustered index IX_NC_Comm_Output on dbo.Commissions_Output([Extended Sales Price])
include (company, location, account, salesroute, employee, producttype, item, loadjdate, commissionrate, [delivered qty])

您測試過的非聚集索引不是這個查詢的最佳選擇。它可以用於WHERE子句和進行索引掃描而不是全表掃描，但不能用於GROUP BY.

最好的索引必須是部分索引（以從WHERE子句中過濾不需要的行），然後在 中使用所有列，然後在 中使用GROUP BY所有INCLUDE其他列SELECT：

CREATE INDEX special_ix 
 ON dbo.Commissions_Output
   ( company, location, account, 
     salesroute, employee, producttype, 
     item, loadjdate, commissionrate ) 
INCLUDE 
 ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
 ( [Extended Sales Price] <> 0 ) ;

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