使用 OR 條件連接兩個臨時表時減少臨時表掃描

我正在處理一個複雜的查詢，到目前為止我已經能夠重構以減少執行時間以及掃描和讀取的次數。在查詢的這一點上，我們有兩個結構完全相同的臨時表；不同之處在於一個表是另一個表的子集（一個是使用更大的日期範圍創建的）。這些表是通過在 CTE 中查詢約 6 個物理表、向下過濾等創建的。我在這裡遇到的查詢部分是當我們在三個欄位上連接兩個表時，然後在 where 子句中，我們進一步使用不等式運算符和 OR 條件比較表中的 5 列。這個查詢似乎是整個批次中成本最高的，大約 200,000 次邏輯讀取和 30,000 多次表掃描。

https://www.brentozar.com/pastetheplan/?id=H16jNYvXK

如您所見，我們正在對臨時表進行表掃描，然後進行合併連接。該計劃看起來還不錯，只是合併連接的行估計太高了

$$ est: 38335 vs actual: 209 $$. 我確實試圖為臨時表創建索引，部分是出於絕望。在這種情況下，它似乎沒有幫助。我測試的索引是使用連接條件中的三個欄位的非聚集索引。這只是將執行計劃更改為在堆中使用 RID 查找，並沒有改變估計或減少掃描/讀取次數。我還嘗試了對 WHERE 子句中使用的欄位的非聚集索引，但由於幾個欄位是 varchar(max) 欄位（在我之前的架構設計選擇很差，而且我被告知只是處理with)，我不能在索引中使用這些。我已經嘗試將它們向下轉換，但一些索引插入失敗，因為它們的字節太多。不僅，https://www.brentozar.com/archive/2021/08/you-probably-shouldnt-index-your-temp-tables/ ).

出於絕望，我還嘗試在兩個表上創建聚集索引，並將連接欄位作為 PK。這確實大大增加了執行時間。這在某種程度上是意料之中的，但我想為什麼不試一試。

我還嘗試使用 union alls 將其分解為 5 個查詢。不幸的是，這給我留下了重複的行，這是我們無法擁有的，使工作量增加了不小的數量，而聯合則花費了太長時間。

更糟糕的是，這部分查詢背後有一個聯合，另一個查詢與更糟糕的 where 子句條件極為相似，因此在這裡弄清楚這一點至關重要。

為什麼我會得到如此多的讀取和掃描，在這種情況下如何減輕這種情況？我很感激你的時間！如果我遺漏了一些重要資訊，請告訴我，我會盡我所能提供。謝謝！

測試

此查詢執行時間約為 1.2 秒，因此我不確定您期望有多少改進。在改善查詢的整體時間時，查看掃描和成本等內容通常會產生誤導和徒勞。

我刪除了查詢中的一些列，只是為了更輕鬆地突出顯示更改。

在頂部，可能值得回到該SELECT INTO #...方法，或者向 select 添加一個tabblock提示以允許並行計劃。您可能不會得到一個，而且由於您的查詢計劃顯示EstimatedAvailableDegreeOfParallelism="2".

總體而言，這可能是一個限制因素。

INSERT INTO 
   #temp WITH(TABLOCK)
select 
  IPS.ACCT as ACCT
 /*SNIP*/
from 
 #SmallerRange IPS
 join #LargerRange V on V.VID = IPS.VID and V.IorO = IPS.IorO and V.OtherLocationID = IPS.OtherLocationID 
WHERE  
(
  IPS.CDate <> V.CDate  or 
  IPS.ACCT != V.ACCT or 
  IPS.Unit != V.Unit or 
  IPS.ARRVLTIME <> V.ARRVLTIME or 
  IPS.LocationID != V.LocationID
  )
OPTION(HASH JOIN);

由於查詢時間從兩個子分支中的約 443 毫秒躍升至合併連接處的約 1.2 毫秒，我們可以推斷在合併連接處花費了約 743 毫秒。Anton 是正確的，它很可能是多對多合併連接。

為了避免這種情況，我們可以添加一個雜湊連接提示，它沒有多對多類型。

嘗試OPTION(FORCE ORDER);提示也可能是有益的。

關於索引臨時表的觀點，有很多因素需要考慮，但 Brent 的文章中沒有涉及。

• 索引臨時表
• 什麼時候應該索引臨時表？

讀取和掃描是讀取內部排序行源的一部分。我有一個小遊戲來複製這個，看起來需要注意的重要事情是兩個臨時表的大小相似，並且您正在比較一個字元串（這很奇怪，您可能會期望相反的情況是正確的）一個更大且更昂貴的字元串參與排序）。您幾乎肯定希望使用您的相等條件來使用雜湊連接。

我使用Stackoverflow數據庫對錶進行採樣複製了您的Comments表。

如果我們這樣做：

select top 40809 * 
into #LargerRange
from dbo.comments

select top 38193 * 
into #SmallerRange
from #LargerRange

生成數據集，並使用

select sr.id
from  #SmallerRange sr
join  #LargerRange  lr
 on  sr.PostId = lr.PostId
and  sr.UserId = lr.UserId
where sr.score <> lr.score 
or    sr.id    <> lr.id    
or    sr.text  <> lr.text

作為您的主要查詢的基礎。您將獲得您看到的合併加入計劃。

如果我們去掉字元串列的比較，我們得到這個雜湊連接計劃。

如果我們更改比較以將字元串轉換為數字（使用散列函式）：

select sr.id
from  #SmallerRange sr
join  #LargerRange  lr
 on  sr.PostId = lr.PostId
and  sr.UserId = lr.UserId
where sr.score <> lr.score 
or    sr.id    <> lr.id    
or    checksum(sr.text)  <> checksum(lr.text)

我們還得到了一個雜湊連接計劃。

如果我們改變它，而不是一堆ORs，我們使用一個 case when 語句（這對於查詢規劃器來說很難分開）：

select sr.id 
from  #SmallerRange sr
join  #LargerRange  lr
 on  sr.PostId = lr.PostId
and  sr.UserId = lr.UserId
where case when sr.score <> lr.score 
            or sr.id    <> lr.id 
            or lr.text  <> lr.text 
          then 1 
          else 0 
    end = 1 

您還可以獲得雜湊連接計劃

該case when選項將是我的偏好。

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