最近線上系統突然出現匯出資料超過 10 筆時，查詢逾時的狀況，在仔細查找之後。 發現了問題原因，透過應用端與數據端兩邊同時調整，將查詢的效率提昇了約數百倍以上 首先，原本應用端的商務邏輯為每一分頁筆數固定為10筆，所以使用者最多可以匯出 10 筆資料 而且原本的商務邏輯是寫成這樣的 這段語法在 SQ ...
最近線上系統突然出現匯出資料超過 10 筆時，查詢逾時的狀況，在仔細查找之後。
發現了問題原因，透過應用端與數據端兩邊同時調整，將查詢的效率提昇了約數百倍以上
首先，原本應用端的商務邏輯為每一分頁筆數固定為10筆，所以使用者最多可以匯出 10 筆資料
而且原本的商務邏輯是寫成這樣的
if (condition.LCKeys != null && condition.LCKeys.Count > 0)
{
sql += "AND (LTRIM(RTRIM(STR(T2.[LawNo],20,3)))+LTRIM(RTRIM(STR([LCNo],20,6)))) IN @LCKeys "; //問題點 (IN參數與欄位用了函數做處理)
dynamicParams.Add("@LCKeys", condition.LCKeys);
}
這段語法在 SQL Server 執行時，會轉換成以下的範例語法
(這裡 A 表 與 B表 為 1:N 關聯 )
SELECT LawNo,LCNo
FROM A INNER JOIN B
WHERE 1=1 AND LTRIM(RTRIM(STR(LawNo+LCNo))) IN (‘A1’,’A2’….’A10’)
在IN條件少的情況下，即使效率不好，還是可以進行資料匯出的
這次的問題是因為應用商務邏輯修改導致，從原本使用者最多只能匯出 10 筆資料
改為最多能匯出500筆資料.
從上述的範例可知 WHERE 條件式的 IN 參數就達 500 個
此外條件式欄位也因用了函數而走 Index Scan
在多個參數下，搜索時間自然拉長
來看看修改前的樣子
在上圖中測試語句只放了10個 IN 條件參數，可以見到執行效率整體不是挺好的
接下來看看我們如何做應用與數據端的調整
首先將應用端原本的查詢參數，改為 Table Valued Parameter 形式
並且將原本的 IN 查找語法，修改為與Table Valued Parameter 做 INNER JOIN
var dynamicParams = new DynamicParameters();
var conditionSection = "";
DataTable dt = new DataTable();
dt.Columns.Add("LawNo");
dt.Columns.Add("LCNo");
for (int index = 0; index < condition.LCNos.Count; index++)
{
DataRow row = dt.NewRow();
row["LawNo"] = condition.LawNos[index];
row["LCNo"] = condition.LCNos[index];
dt.Rows.Add(row);
}
if (dt.Rows.Count > 0)
{
conditionSection = " INNER JOIN @LawsTempx X ON X.LawNo = T2.LawNo AND X.LCNo = T2.LCNo "; //加上額外的 INNER JOIN 條件
dynamicParams.Add("LawsTempx", dt.AsTableValuedParameter("LawsTemp")); //將DataTable轉換成TVP傳入數據庫
}
接下來在數據端需要建立一個對應的自訂 Table Type
最後來看看修改後的結果
可以看到整個修改完後，Logical read 數下降了快4萬之多，執行計畫也移掉了 Parallelism 執行
是不是感覺很簡單呢？這個用法不只能用在查找語句的改寫，也可以試著用在大量 Insert 緩慢的情境上
各位小夥伴，如果應用端突然出現這種情況的話，可以考慮看看與開發溝通，改用 TVP 進行改寫喲 
謝謝大家
補充： 表值參數 (TVP) 的限制
https://docs.microsoft.com/zh-cn/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017#Restrictions
有下麵的限制：
-
SQL Server 不維護表值參數列的統計信息。
-
表值參數必須作為輸入 READONLY 參數傳遞到 Transact-SQL 常式。 不能在常式體中對錶值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
-
不能將表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目標。 表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字元串或存儲過程中。
