Sql-Server

如何有效地重新計算相當大的 X 表中的數據？

  • October 5, 2020

我最近注意到我現在正在開發的主要應用程序的性能問題（它是一個遺留應用程序）。一個作業觸發一個儲存過程，該過程計算數據庫中所有文件和所有應用程序使用者的安全性。該表如下所示：

Id INT IDENTITY(1, 1) CONSTRAINT PK_DocumentSecurity PRIMARY KEY CLUSTERED,
DocumentId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES Document,
UserId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES ApplicationUser,
CanWrite BIT NOT NULL,
+ an index for DocumentId and UserId

該表在生產中大約有 360 萬條記錄，但由於文件和使用者數量的增加，這一數字還在不斷增長。

這種情況經常發生（每分鐘一次），我注意到使用 sp_who2 有時會阻止其他 SPID。profiler 還顯示了非常大量的讀取和 CPU，因此很明顯，這個過程非常繁重。

有時，在測試環境中，計算似乎需要很長時間，這可能是由於不時發生的非常低效的查詢計劃（測試伺服器的資源顯著低於生產伺服器）。

我檢查了程式碼，目前算法如下所示：

  • 為計算中涉及的表中的所有記錄計算雜湊
  • 如果雜湊和以前一樣，什麼也不做
  • 插入一個大而復雜的 SELECT 語句的結果（我將在下面提供詳細資訊）以插入到具有上述確切模式的持久表中
  • 完成後，用於連接以應用安全性的視圖被更改為使用全新的計算表

實際計算如下所示（由於簡潔，刪除了實際細節）：

SELECT DISTINCT ISNULL(ds.UserId, 0), ISNULL(ds.DocumentId, 0)
FROM (
   SELECT ... FROM Document ... CROSS JOIN ApplicationUser ...
   UNION
   SELECT ... FROM Document ... CROSS JOIN ApplicationUserRole ...
   UNION
   SELECT ... FROM Document ...
   UNION 
   SELECT DISTINCT ... FROM DocumentDetail ...
   UNION 
) ds
JOIN (
   SELECT ... FROM Document ...
   WHERE (... OR ... OR ... ) AND ...
   UNION
   SELECT ...
) dsp ON dsp.DocumentId = ds.DocumentId AND dsp.UserId = ds.UserId

從我的角度來看，這種計算方式存在兩個大問題：

  • 維護：引入新的安全規則變得越來越難
  • 性能：OR 和 DISTINCT 正在扼殺性能

我已經創建了一個故事來重構這段程式碼，但我想在開始處理之前驗證我的推理是正確的（我有 OOP 偏見，因為我主要是 .NET 開發人員）：

  • 重構安全規則（由業務部門驗證，因為它們看起來肯定與已經存在的不同，對於所有情況也可能不是 100% 正確），它們都喜歡以下內容（此處優先事項）：

如果角色是管理員，則允許訪問所有文件如果角色是銷售並且

使用者具有文件的讀取角色，則使用 CanWrite = 0 插入 如果角色是

銷售並且使用者具有文件的寫入角色，則使用 CanWrite = 1 插入

  • 使用目標架構創建一個臨時表
  • 為 DocumentId 和 UserId 創建索引
  • 應用每個安全規則：
INSERT INTO #buffer (DocumentId, UserId, CanWrite)
SELECT ...
FROM Document D ...
 JOIN ApplicationUser U ...
WHERE NOT EXISTS (SELECT 1 FROM #buffer B WHERE B.DocumentId = D.DocumentId AND B.UserId = U.UserId)
  • 截斷目標表
  • 插入 DocumentSecurity SELECT * FROM #buffer

如果最後兩個步驟花費的時間太長，則可能需要對其進行優化（可能替換為已經存在的視圖機制下的交換錶）。

這應該使程式碼更易於維護，並且我認為如果我刪除所有 OR 和 DISTINCT，性能會更好，但我對 DBA 對這種方法的看法感興趣。它是次優的嗎？

注意：我不能在安全表中有重複項，因為目前有很多地方（應用層中使用的實體框架 + 儲存過程）只是簡單地與安全視圖連接（而不是檢查是否存在）。

我們在這裡談論的數據量還不足以成為這麼大的問題。奇怪的是，一些優化會對你有好處。

因此，隨著我的進展，打開您的查詢計劃：

  • 修正你的WHERE條款。由於您使用函式，您目前的查詢幾乎在任何地方都強制進行表掃描。IsNull(COALESCE(linkedlot.IsPrivate, c.IsPrivate), 0) = 0應該是((ll.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate Is Null))。旁注：這些欄位實際上可以為空嗎？為什麼？如果不是，這些查詢會簡單得多。
  • 更改UNION為UNION ALL。UNION做了一個隱含的DISTINCT，但你已經在主查詢中做了一個。根據您的數據，這可能會有所幫助或傷害很多。
  • 確保您的臨時表具有索引/主鍵。
  • 如果查詢實際上需要一段時間，請將其載入到表的副本中，然後再將MERGE其載入到實時副本中。

等等，沒關係……很簡單，你CROSS JOIN的 s 正在殺死你。

查看您的查詢計劃（或者更確切地說，保存它並在 SQL Server Management Studio 中打開它），並將滑鼠懸停在其中一些粗箭頭上。您爆炸了Contract五次ApplicationUserRole（每次 800 萬行），Contract兩次ApplicationUser（每次 600 萬行），然後將所有這些組合在一起（在幾個級別上）以獲得不同的值（每次排序 8-1600 萬行）。

但是“針對可能影響權利的所有可能方式展開所有可能的組合，無論它們是否存在，然後組合，然後排序，然後再次排序”不是你想要的。您想要使用者與文件，並查找他們的權限。

所以讓我們這樣做。

您的查詢應如下所示：

Select
 UserId = U.UserId,
 ContractId = C.ContractId,
 -- If there are explicit denials, MAX should be MIN
 CanRead = Max(Case When <<highest priority condition>>
                    When <<down the list...>>
                    Else 0 End),
 -- If there are explicit denials, MAX should be MIN
 CanWrite = Max(Case When <<highest priority condition>>
                     When <<down the list...>>
                     Else 0 End)
From
 dbo.User As U
 Cross Join dbo.Contract As C
 Left Outer Join (<<Look up first thing...>>)
 Left Outer Join (<<down the list...>>)
Group By
 U.UserId, C.ContractId

所以，從你的計劃中獲取一些：

Select
 UserId = U.UserId,
 ContractId = C.ContractId,
 CanRead = Max(Case When ContractOwner.UserId Is Not Null Then 1
                    When <<down the list...>>
                    Else 0 End),
 CanWrite = Max(Case When ContractOwner.UserId Is Not Null Then 1
                     When <<down the list...>>
                     Else 0 End)
From
 dbo.User As U
 Cross Join dbo.Contract As C
 Left Outer Join dbo.LotSupplier As LotSupplier
   On LotSupplier.ContractId = C.Id
 Left Outer Join dbo.ContractBuyer As ContractBuyer 
   On ContractBuyer.ContractId = C.Id
 Left Outer Join dbo.Lot As LinkedLot 
   On LotSupplier.LotId = LinkedLot.Id
 Left Outer Join dbo.ApplicationUserRole As ContractOwner
   On ContractOwner.UserId = U.UserId
   And ContractOwner.BUid = C.SignatoryBUId
   And ((C.IsPrivate Is Null) Or (C.IsPrivate = Cast(0 As Bit)))  -- NO ISNULL!!
   And ContractOwner.RoleId In (16, 19, 20)
 Left Outer Join dbo.ContractBuyer As ContractBuyer
   On ContractBuyer.ContractId = C.Id
 Left Outer Join dbo.ApplicationUserRole As Buyer
   On ((C.IsPrivate = Cast(1 As Bit)
       Or (LinkedLot.IsPrivate = Cast(1 As Bit))
   And ContractBuyer.BuyerId = Buyer.UserId
   And ...on and on...
 Left Outer Join (<<down the list...>>)
Group By
 U.UserId, C.ContractId

除非你在一個極度缺乏規範的伺服器上執行它，否則整個查詢可以在幾秒鐘內執行，相信我。

由於您顯然也在使用我所說的多帽子模型（一個人可以是使用者、買家、所有者等，我強烈建議您考慮規範化一個人（或派對，或任何你喜歡的東西）桌子。

  • 資訊只能在一個地方更新
  • 您不必在電子郵件上進行那種糟糕的連接
  • 該加入不會在僅在一個地方更新資訊時失敗:-)
  • 您會更好地進行分包、多所有者承包、契約轉讓和所有有趣的設置

看起來您是出於此目的使用 ApplicationUser ，但我認為僅您的問題就表明了其中一個缺點。

你是什ReComputing​​麼？以及你是如何重新計算的不是那麼清楚？

避免的建議DISTINCT，UNION已經給出。

我不知道 CROSS JOIN您查詢的目的是什麼以及是否可以避免。

如果CROSS JOIN生產的不是多行而不是少行，那麼它不會影響性能。

FROM Document而不是一次又一次地查詢將結果放在#Temp表中。

不確定在你的情況下是否值得放在#Temp桌子上。

這種情況經常發生（每分鐘一次），我注意到使用 sp_who2 有時會阻止其他 SPID。profiler 還顯示了非常大量的讀取和 CPU，因此很明顯，這個過程非常繁重。

原因之一是Foriegn Key Constraint。這是FK約束的缺點之一。

當您已經DocumentId,Userid從他們各自的表中進行驗證時，為什麼要放置FK Constraint.

一旦你可以嘗試禁用FK Constraint.

或創建Trusted FK 約束

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

相關問答

Sql-Server

缺乏索引對支持大型生產數據庫的影響

  • May 12, 2022
檢視問答
Sql-Server

在 AG 中記錄發送隊列大小和重做隊列大小

  • April 1, 2022
檢視問答
Sql-Server

如何在 CPU 時間方面減少 Temp Table 創建時間？

  • January 7, 2022
檢視問答
Sql-Server

查詢儲存需要很長時間才能載入持續時間

  • December 14, 2021
檢視問答
Sql-Server

緩衝池到底有多重要？

  • December 7, 2021
檢視問答
Sql-Server

更新“實時”表上數據的最佳實踐

  • October 31, 2021
檢視問答