搜尋特定值時查詢性能較慢，但在同一列上使用不同值時查詢性能較快

February 16, 2018

我有一個由前端應用程序執行的 SQL Server 2017 選擇語句。該查詢具有三個連接和UNION. 每個連接語句都有一個 where 子句NAME LIKE '%ibm%'。

當我更改%ibm%為NAME LIKE %services%像往常一樣快速執行時。

我重建了所有表上的所有索引。我使用 SQL Server Profiler 和 Database Tuning Advisor 來分析查詢。它發現我創建的某些列缺少統計資訊。它還建議創建三個過濾索引視圖，但這不是我們流程中的選項。所有這些都沒有改變查詢性能。

詢問

SELECT  companyName FROM (
      SELECT  TOP 25  upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
      FROM
      MyDB.test.CompanyProfile CP WITH (NOLOCK)
      INNER JOIN  MyDB.test.OrgTeaming orgTeaming WITH (nolock) ON orgTeaming.OrgId = CP.OrgID
      WHERE (1=0    OR OrgTeamingNameTx LIKE '%ibm%')
UNION
      SELECT  TOP 25 upper(rtrim(ltrim(Vendor.NameTx))) AS companyName
      FROM
      MyDB.test.CompanyProfile CP WITH (NOLOCK)
      INNER JOIN MyDB.test.Vendor Vendor WITH (NOLOCK) ON CP.VendorID = Vendor.VendorID
      WHERE (1=0 OR Vendor.NameTx LIKE '%ibm%')
UNION
      SELECT  TOP 25 upper(rtrim(ltrim(vd.Company_Name))) companyName
      FROM
      MyDB.test.VendorRegistrationPOCPersisted vr WITH (NOLOCK)
      INNER JOIN  MyDB.test.VendorDNB vd WITH (NOLOCK) ON vr.VendorID = vd.VendorID
      WHERE (1=0 OR vd.Company_Name LIKE '%ibm%')
) AS companyInfo
ORDER BY companyName ASC

實時統計

時間和 IO 統計

使用 ‘%services%’ 是 0 秒。

===============================================================
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

(70 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorDNB'. Scan count 4637, logical reads 23529, physical reads 50, read-ahead reads 1014, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorRegistrationPOCPersisted'. Scan count 1, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CompanyProfile'. Scan count 50, logical reads 205, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Vendor'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrgTeaming'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*SQL Server Execution Times:
  CPU time = 78 ms,  elapsed time = 244 ms.*

使用 ‘% ibm %’ 差不多 3 秒。

*SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.*

*SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.*

*SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.*

(25 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorRegistrationPOCPersisted'. Scan count 1, logical reads 18501, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorDNB'. Scan count 1, logical reads 17819, physical reads 0, read-ahead reads 51, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CompanyProfile'. Scan count 35, logical reads 132, physical reads 0, read-ahead reads 62, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Vendor'. Scan count 1, logical reads 7782, physical reads 0, read-ahead reads 1279, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrgTeaming'. Scan count 1, logical reads 1472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*SQL Server Execution Times:
  CPU time = 2875 ms,  elapsed time = 2894 ms.*

附加資訊

當我使用時%services%，我總共得到 25 條記錄；當我使用 67,114% ibm %
當我使用一個絕對不存在的字元串時，%ZZQQXXFF%它只需要 2 秒
我嘗試刪除1=0並替換UNION為UNION ALL，但這並沒有導致物理掃描、邏輯讀取和 CPU 時間方面的任何改進。
1=0附加以形成對選擇欄位的動態查詢，以涵蓋使用者沒有輸入任何搜尋詞並簡單地點擊下拉列表的情況。
添加USE HINT('DISABLE_OPTIMIZER_ROWGOAL')並沒有提高查詢性能。
覆蓋索引是不可能的，因為使用者可以按任何值進行搜尋。

查詢：

SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
FROM MyDB.test.OrgTeaming orgTeaming
WHERE OrgTeamingNameTx LIKE '%ibm%'

好像：

SQL Server parse and compile time: 
  CPU time = 10 ms, elapsed time = 10 ms. 

(1 row affected)

Table 'CompanyProfile'. Scan count 10, logical reads 30, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OrgTeaming'. Scan count 1, logical reads 1607, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 16 ms,  elapsed time = 20 ms.

讓我們從一個更簡單的問題開始。這個查詢的性能會是什麼樣子？
SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
FROM MyDB.test.OrgTeaming orgTeaming
WHERE OrgTeamingNameTx LIKE '%ibm%'
在表達式的兩邊都放一個萬用字元LIKE意味著 SQL Server 沒有很多選項。您在表上定義了一個聚集索引，因此它將掃描一個索引，直到找到 25 行或用完要掃描的行。如果有一個較小的覆蓋索引，其中包含查詢所需的列，則查詢優化器可能會為查詢選擇該索引。掃描頁面較少的對象會更便宜。您的查詢計劃顯示了兩個聚集索引掃描。使用萬用字元搜尋在所有表上創建較小的覆蓋索引可能會提高性能，尤其是在表中沒有 25 個匹配項的情況下。
回到問題中的查詢，從性能的角度來看，另一個重要部分與如何將連接實現到其他表有關。根據STATISTICS IO輸出，查詢優化器對您要過濾的內容做出明顯不同的選擇。問題中沒有足夠的資訊可以說更多。引入了一個行目標，TOP如果 SQL Server 在聚集索引掃描期間掃描的行數多於預期，則可能導致計劃選擇不理想。我的建議是查看慢速計劃的實際和估計行數，並尋找顯著差異。您還可以比較慢速和快速計劃的差異。最後一個選項是使用慢速查詢嘗試USE HINT('DISABLE_OPTIMIZER_ROWGOAL')查詢提示，看看會發生什麼。

如果 MyDB.test.CompanyProfile 與 MyDB.test.OrgTeaming 的關係是一對一的，則使用內部聯接。如果是一對多，則使用 EXISTS 子句。並看到兩列都是索引。與其他列類似，
CREATE table #tmp(companyName varchar(200) not null)

INSERT INTO #tmp (companyName)
SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
INNER JOIN MyDB.test.OrgTeaming orgTeaming WITH (NOLOCK) ON orgTeaming.OrgId = CP.OrgID
WHERE (
       1 = 0
       OR OrgTeamingNameTx LIKE '%ibm%'
       )
ORDER BY OrgTeamingNameTx ASC
要麼 -
SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
WHERE EXISTS (
       SELECT 1
       FROM MyDB.test.OrgTeaming orgTeaming WITH (NOLOCK)
       WHERE orgTeaming.OrgId = CP.OrgID
       )
   AND (
       1 = 0
       OR OrgTeamingNameTx LIKE '%ibm%'
       )
ORDER BY OrgTeamingNameTx ASC
如果你有“ distinct type reason”，那麼無論如何都要使用UNIONelse 。UNION ALL
在此解決方案中，如果您需要過濾不同的記錄，則可以使用EXISTS子句來執行此操作。
使用與上述相同的邏輯編寫第二個查詢
INSERT INTO #tmp (companyName)
SELECT TOP 25 upper(rtrim(ltrim(Vendor.NameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
INNER JOIN MyDB.test.Vendor Vendor WITH (NOLOCK) ON CP.VendorID = Vendor.VendorID
WHERE (
       1 = 0
       OR Vendor.NameTx LIKE '%ibm%'
       )
-- don't use if it is not required
-- and not exists(select 1 from #tmp t where t.companyName=Vendor.NameTx)
ORDER BY Vendor.NameTx
同樣寫第三個查詢
您還可以找到 3 個查詢中的哪一個更令人反感。

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

搜尋特定值時查詢性能較慢，但在同一列上使用不同值時查詢性能較快

詢問

實時統計

時間和 IO 統計

附加資訊

相關問答

SQL Server 2008r2、2017 和 2019 之間的查詢執行異常

計算表中單詞的出現速度很慢

生產中的查詢慢、執行計劃錯誤或索引錯誤？

只讀副本上的長時間執行查詢，在主副本上需要一些時間

誰能幫我完成這個糟糕的查詢計劃？

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