如何通過單個表上不同數據類型的多個欄位來提高查詢的執行時間？

餐桌設計

在 SQL Server Microsoft SQL Server Enterprise Evaluation（64 位）版本 12.0.5207.0 上，我有這個表，稱為 InputResult：

表有 11.645.408 行。

ValueAsString、ValueAsInteger、ValueAsNumber、ValueAsDateTime、ValueAsTime、ValueAsListGuids、ValueAsGuid 列對於一條記錄是互斥的（一條記錄只能填寫一列）。

索引

主鍵 - 聚集索引

分片：

非集群

關鍵列：

包含的列：

分片：

全文：

詢問

   select 
      [Id]
     ,[Occurrence_id]
     ,[AtsReport_id]
from InputResult
where 
  (InputUniqueId = '5D797CE2-F8C6-4790-B9F4-1D04FD9348D5' and contains(ValueAsString,'"asd*"'))

or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'LRBB'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber=6575)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'asd2223'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime>=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger<=1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime<=54000000000)

-------------------------------------------------------------------------------------------
or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'"LRBB*"'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber<655)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'asd2223'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime>=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime<=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime<=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger<=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger>1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime<=54000000000)

------------------------------------

or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'asd'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber>6575)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'aaa'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime>=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime>=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger>=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger<1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime=54000000000)

結果

第一次在 SSMS 中執行查詢時，需要 02:00 分鐘才能完成（返回 782.508 行）。

後續執行時間：7 秒

查詢執行計劃

查詢執行計劃為 XML

我已經重建了索引，現在碎片看起來像這樣：

重建索引後，相同查詢的執行需要 8 / 9 秒。

我的問題是：如何優化此查詢？

如果您要在 SSMS 中對具有不小的結果集的查詢進行性能測試，那麼您應該禁用結果集的呈現。看起來您的查詢花費了幾秒鐘的時間。在 SSMS 中執行查詢沒有任何問題，但如果你不小心，你最終可能會嘗試調整一些不可調整的東西。

重建索引確實減少了其中的頁數。但是，這還不足以解釋 17 倍的性能提升。重建索引也可能會有所幫助，因為它會更新統計資訊並將您需要的數據放入緩衝池。話雖如此，您有一個聚集鍵的 guid，因此您可能會遇到索引碎片問題。如果你有這樣的問題，那麼你唯一的選擇就是忍受它，不斷地進行維護，或者改變表的結構。

這並不意味著不可能讓您的查詢執行得更快。由於上述（和其他原因），我將專注於 CPU 以供您查詢。查看執行時操作員統計資訊，我可以得到以下計劃不同部分的 CPU 細分：

• 對[index_by_values]索引進行掃描和過濾需要 9300 毫秒
• 全文索引操作（包括索引假離線）需要 7500 毫秒的 CPU 時間
• 2000 ms 用於與全文部分相關的其他操作
• 查詢的其他部分為 2200 毫秒

[index_by_values]索引沒有正確的查詢鍵列順序。您希望該InputUniqueId列位於第一位，以便引擎可以使用該列進行搜尋。照原樣，您正在掃描完整索引並將過濾器作為單獨的過濾器運算符應用。

查詢的全文部分將不太容易改進。我幾乎沒有使用全文索引的經驗，但我懷疑如果您重寫查詢以便它只執行一到兩次全文索引操作而不是 10 次，您會獲得更好的性能。至少，對於您目前的查詢計劃所有全文操作和索引假離線都在嵌套循環的內側，這意味著它們不能並行建構。如果您查看這些運算符的實際行數，您會發現 8 個執行緒中有 7 個沒有做任何工作。我假設MAXDOP 8它適合您的系統，如果您可以為查詢的全文部分獲得適當的並行性，您的查詢可能會更快。希望獲得併行的單一全文 TVF 的一種方法是使用臨時表。下面是一個簡化的例子：

SELECT id,
CAST(CASE WHEN contains(ValueAsString,'asd')) THEN 1 ELSE 0 END AS BIT) BIT_asd
CAST(CASE WHEN contains(ValueAsString,'aaa')) THEN 1 ELSE 0 END AS BIT) BIT_aaa
into #fts_inputResult
FROM InputResult
WHERE contains(ValueAsString,'asd')) OR contains(ValueAsString,'aaa'));

id根據插入臨時表的行數，在列上添加聚集索引可能會有所幫助。在任何情況下，您都可以將查詢更改為簡單地加入臨時表，而不是將包含邏輯作為選擇的一部分。

應該可以提高查詢的性能，但是您沒有說明您的目標，所以我不知道以上是否足夠。祝你好運。

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