使索引在連接到表變數時起作用

我是一個有幾列的表，但最重要的是DocumentId(bigint)：StatusId(int)和ReceiveDateTime(datetimeoffset(3))。

CREATE TABLE [DocumentInfo](
   [DocumentId] [bigint] NOT NULL,
   [StatusId] [int] NOT NULL,
   [ReceiveDateTime] [datetimeoffset](3) NOT NULL,
   [IsDeleting] [bit] NOT NULL,
   [StorageId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED 
(
   [DocumentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

我還有一個包含多行的表變數（現在為 4 行）：

DECLARE @Tresholds TABLE
(
   DocumentStatusId int NOT NULL,
   Treshold datetime2 NOT NULL
);

我在 DocumentStatusId 上使用了索引（是一個主聚集鍵）和唯一約束（Treshold，DocumentStatusId），但查詢沒有使用它，所以我刪除了它們。

我的查詢看起來像：

WITH TopOutdatedDocuments AS
(
   SELECT TOP (100000) di.DocumentId
   FROM [DocumentInfo] di WITH (NOLOCK)
   JOIN @Tresholds t ON di.StatusId = t.DocumentStatusId
   WHERE (ReceiveDateTime < @DocOldestTreshold OR (ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold))
   AND NOT EXISTS (SELECT * FROM [doc].[document] d (NOLOCK) WHERE d.ParentId = di.DocumentId)
) 

查詢計劃總是使用索引掃描的問題：

我試圖為這 3 列的每個排列創建一個索引，但它無論如何都沒有使用它。DocOldestTreshold並@DocNewestTreshold用於加速查詢，因為它們可以在沒有連接的情況下計算。但似乎引擎並沒有利用這個機會。

如何加快查詢速度？它的邏輯意義是我們對每個都有一些門檻值StatusId，如果文件比它的門檻值更舊，它應該被這個查詢選中。

任何幫助將不勝感激。我現在在 SQL Server 2014 上。 order of[DocumentInfo]的大小為數億行，典型大小@Tresholds為 < 10。

假設你的變數總是NOT NULL

WHERE  ( ReceiveDateTime < @DocOldestTreshold
         OR ( ReceiveDateTime < @DocNewestTreshold
              AND ReceiveDateTime < t.Treshold ) ) 

相當於

WHERE  ReceiveDateTime < @DocOldestTreshold
       OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold) 

這相當於

WHERE  ReceiveDateTime < GREATEST(@DocOldestTreshold, LEAST(@DocNewestTreshold, t.Treshold)) 

所以如果你有這個索引

CREATE INDEX ix
 ON [DocumentInfo](StatusId, [ReceiveDateTime])

以及以下查詢

SELECT di.DocumentId
FROM   @Tresholds T
      CROSS APPLY (SELECT IIF(@DocNewestTreshold < t.Treshold, @DocNewestTreshold, t.Treshold)) CA1(Least)
      CROSS APPLY (SELECT IIF(@DocOldestTreshold > CA1.Least, @DocOldestTreshold, CA1.Least)) CA2(Target)
      JOIN [DocumentInfo] di
        ON di.StatusId = t.DocumentStatusId
           AND di.ReceiveDateTime < CA2.Target
           AND NOT EXISTS (SELECT *
                           FROM   [doc].[document] d (NOLOCK)
                           WHERE  d.ParentId = di.DocumentId) 

它可以進行平等搜尋StatusId和範圍搜尋ReceiveDateTime

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