Sql-Server

為什麼這樣更快並且使用安全？（字母表中的第一個字母）

  • January 19, 2019

長話短說，我們正在用一個非常大的人表中的值更新小人表。在最近的測試中，此更新需要大約 5 分鐘才能執行。

我們偶然發現了看似最愚蠢的優化，這似乎完美無缺！現在，相同的查詢在不到 2 分鐘的時間內執行，並完美地產生了相同的結果。

這是查詢。最後一行被添加為“優化”。為什麼查詢時間急劇減少？我們錯過了什麼嗎？這會導致將來出現問題嗎？

UPDATE smallTbl
SET smallTbl.importantValue = largeTbl.importantValue
FROM smallTableOfPeople smallTbl
JOIN largeTableOfPeople largeTbl
   ON largeTbl.birth_date = smallTbl.birthDate
   AND DIFFERENCE(TRIM(smallTbl.last_name),TRIM(largeTbl.last_name)) = 4
   AND DIFFERENCE(TRIM(smallTbl.first_name),TRIM(largeTbl.first_name)) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(TRIM(largeTbl.last_name), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')

技術說明：我們知道要測試的字母列表可能需要更多字母。我們也意識到使用“差異”時明顯的誤差範圍。

查詢計劃（正常）： https ://www.brentozar.com/pastetheplan/?id=

rypV84y7V 查詢計劃（帶有“優化”）： https ://www.brentozar.com/pastetheplan/?id=r1aC2my7E

這取決於您的表中的數據、您的索引、….如果無法比較執行計劃/io + 時間統計資訊，很難說。

我期望的區別是在兩個表之間的 JOIN 之前發生的額外過濾。在我的範例中，我將更新更改為選擇以重用我的表。

帶有“優化”的執行計劃 在此處輸入圖像描述

執行計劃

您清楚地看到發生了過濾操作，在我的測試數據中沒有過濾掉任何記錄，因此沒有進行任何改進。

執行計劃，沒有“優化” 在此處輸入圖像描述

執行計劃

過濾器消失了，這意味著我們將不得不依靠連接來過濾掉不需要的記錄。

其他原因 更改查詢的 另一個原因/結果可能是，在更改查詢時創建了一個新的執行計劃，這恰好更快。這方面的一個例子是引擎選擇不同的 Join 運算符，但這只是猜測。

編輯：

得到兩個查詢計劃後澄清：

該查詢正在從大表中讀取 550M 行，並將它們過濾掉。 在此處輸入圖像描述

這意味著謂詞是進行大部分過濾的謂詞，而不是搜尋謂詞。導致數據被讀取，但返回的方式更少。

讓 sql server 使用不同的索引（查詢計劃）/添加索引可以解決這個問題。

那麼為什麼優化查詢沒有同樣的問題呢？

因為使用了不同的查詢計劃，使用掃描而不是查找。

在此處輸入圖像描述 在此處輸入圖像描述

不做任何搜尋，但只返回 4M 行來處理。

下一個區別

忽略更新差異（優化查詢上沒有更新任何內容），在優化查詢上使用雜湊匹配：

在此處輸入圖像描述

而不是非優化的嵌套循環連接：

在此處輸入圖像描述

當一張桌子很小而另一張桌子很大時，嵌套循環是最好的。由於它們都接近相同的大小，我認為在這種情況下雜湊匹配是更好的選擇。

概述

優化後的查詢 在此處輸入圖像描述

Optimized query的plan具有並行性，使用hash match join，需要做的residual IO過濾比較少。它還使用點陣圖來消除不能產生任何連接行的鍵值。（也沒有任何更新）

非優化查詢 在此處輸入圖像描述 非優化查詢的計劃沒有並行性，使用嵌套循環連接，需要對 550M 記錄做殘差 IO 過濾。（更新也在進行中）

您可以做些什麼來改進非優化查詢？

  • 將索引更改為在鍵列列表中包含 first_name 和 last_name：

在 dbo.largeTableOfPeople(birth_date,first_name,last_name) 上創建索引 IX_largeTableOfPeople_birth_date_first_name_last_name 包括 (id)

但是由於函式的使用和這個表很大，這可能不是最佳解決方案。

  • 更新統計數據，使用重新編譯來嘗試獲得更好的計劃。
  • (HASH JOIN, MERGE JOIN)向查詢中添加 OPTION
  • …

測試數據 + 使用的查詢

CREATE TABLE #smallTableOfPeople(importantValue int, birthDate datetime2, first_name varchar(50),last_name varchar(50));
CREATE TABLE #largeTableOfPeople(importantValue int, birth_date datetime2, first_name varchar(50),last_name varchar(50));


set nocount on;
DECLARE @i int = 1
WHILE @i <= 1000
BEGIN
insert into #smallTableOfPeople (importantValue,birthDate,first_name,last_name)
VALUES(NULL, dateadd(mi,@i,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @i += 1;
END


set nocount on;
DECLARE @j int = 1
WHILE @j <= 20000
BEGIN
insert into #largeTableOfPeople (importantValue,birth_Date,first_name,last_name)
VALUES(@j, dateadd(mi,@j,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @j += 1;
END


SET STATISTICS IO, TIME ON;

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
   ON largeTbl.birth_date = smallTbl.birthDate
   AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
   AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å');

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
   ON largeTbl.birth_date = smallTbl.birthDate
   AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
   AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
--AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')




drop table #largeTableOfPeople;
drop table #smallTableOfPeople;

不清楚第二個查詢是否實際上是一種改進。

執行計劃包含的 QueryTimeStats 顯示的差異遠小於問題中所述的顯著差異。

慢速計劃的經過時間為257,556 ms（4 分 17 秒）。儘管執行並行度為 3，但快速計劃的執行時間為190,992 ms（3 分 11 秒）。

此外，第二個計劃是在一個數據庫中執行的，在該數據庫中連接後沒有工作可做。

第一個計劃

在此處輸入圖像描述

第二個計劃

在此處輸入圖像描述

因此，額外的時間可以很好地解釋為更新 350 萬行所需的工作（更新運算符中定位這些行、鎖定頁面、將更新寫入頁面和事務日誌所需的工作不可忽略）

如果在比較like 和like 時這實際上是可重現的，那麼解釋是在這種情況下你很幸運。

具有 37IN個條件的過濾器僅消除了表中 4,008,334 行中的 51 行，但優化器認為它會消除更多

在此處輸入圖像描述

  LEFT(TRIM(largeTbl.last_name), 1) IN ( 'a', 'à', 'á', 'b',
                                         'c', 'd', 'e', 'è',
                                         'é', 'f', 'g', 'h',
                                         'i', 'j', 'k', 'l',
                                         'm', 'n', 'o', 'ô',
                                         'ö', 'p', 'q', 'r',
                                         's', 't', 'u', 'ü',
                                         'v', 'w', 'x', 'y',
                                         'z', 'æ', 'ä', 'ø', 'å' )

這種不正確的基數估計通常是一件壞事。在這種情況下，它產生了一個不同形狀（和平行）的計劃，顯然（？）對你來說效果更好，儘管大量低估導致雜湊溢出。

如果沒有TRIMSQL Server 能夠將其轉換為基列直方圖中的範圍間隔並給出更準確的估計，但TRIM它只是求助於猜測。

LEFT(TRIM(largeTbl.last_name), 1)猜測的性質可能會有所不同，但在某些情況下，對單個謂詞的估計*只是估計為table_cardinality/estimated_number_of_distinct_column_values。

我不確定究竟是什麼情況——數據的大小似乎起了一定的作用。我能夠像這裡一樣使用寬的固定長度數據類型來重現它，但是得到了一個不同的、更高的猜測varchar（它只是使用了 10% 的固定猜測並估計了 100,000 行）。@Solomon Rutzky指出，如果varchar(100)使用尾隨空格來填充char較低的估計值

該IN列表擴展為ORSQL Server 使用指數退避，最多考慮 4 個謂詞。所以219.707估計如下。

DECLARE @TableCardinality FLOAT = 4008334, 
       @DistinctColumnValueEstimate FLOAT = 34207

DECLARE @NotSelectivity float = 1 - (1/@DistinctColumnValueEstimate)

SELECT @TableCardinality * ( 1 - (
@NotSelectivity * 
SQRT(@NotSelectivity) * 
SQRT(SQRT(@NotSelectivity)) * 
SQRT(SQRT(SQRT(@NotSelectivity)))
))

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

相關問答

Sql-Server

為什麼 SQL Server 在將結果選擇到標量變數時不使用 OPTION(RECOMPILE) 執行常量 (UNION ALL) 分支消除？

  • April 4, 2021
檢視問答
Sql-Server

SQL Server 2017 儲存過程和 TVF 性能問題

  • December 17, 2019
檢視問答
Sql-Server

SQL Server 如何在記憶體中記憶體數據

  • October 30, 2019
檢視問答
Sql-Server

相同邏輯的不同表現形式會產生非常不同的執行時間

  • February 16, 2019
檢視問答
Sql-Server

“警告：操作導致殘留 I/O”與鍵查找

  • October 28, 2018
檢視問答
Sql-Server

雜湊匹配溢出

  • August 2, 2018
檢視問答