热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

一次快速改寫SQLServer高效查詢的範例

最近線上系統突然出現匯出資料超過10筆時,查詢逾時的狀況,在仔細查找之後。發現了問題原因,透過應用端與數據端兩邊同時調整,將查詢的效率提昇了約數百倍以上首先,原本應用端的商務邏輯為每一分頁

最近線上系統突然出現匯出資料超過 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

在多個參數下,搜索時間自然拉長

來看看修改前的樣子

imageimage

在上圖中測試語句只放了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  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

image

最後來看看修改後的結果

image

image

可以看到整個修改完後,Logical read 數下降了快4萬之多,執行計畫也移掉了 Parallelism 執行

是不是感覺很簡單呢?這個用法不只能用在查找語句的改寫,也可以試著用在大量 Insert 緩慢的情境上 

各位小夥伴,如果應用端突然出現這種情況的話,可以考慮看看與開發溝通,改用 TVP 進行改寫喲 Smile

謝謝大家

補充: 表值參數 (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 字符串或存储过程中。

 


推荐阅读
author-avatar
xiaoge
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有