帶聯接的大表按性能排序

您好，我有一個包含 206,902 條記錄的表，我正在努力改進我的查詢：

SELECT TOP (50) [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
 FROM [Data]
Inner Join [Permission]
   On ([Data].[ID] = [Permission].[FichaID]
  AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
       OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
       ))
Group By [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
  Order By [Created] desc

查詢執行速度很快，但使用工具 SQLQueryStress 進行 50 次迭代補全的時間為 3 分鐘。如果我通過查詢刪除訂單需要不到一秒鐘的時間。我在數據表上創建了一個索引：

CREATE INDEX facervo_created ON [Data] ([Created] desc);
CREATE INDEX facervo_status ON [Data] ([_ModerationStatus]);
CREATE INDEX facervo_expiration ON [Data] ([Expiration]);
CREATE INDEX facervo_reserva ON [Data] ([Reserva]);

表上的索引權限：

ADD CONSTRAINT perm_key PRIMARY KEY (FichaID, PermID, IsGroup)

在性能上仍然沒有取得很大的成功。可以更快地執行此查詢嗎？

編輯

我刪除了兩個表的所有索引，並創建了這個新索引：

CREATE NONCLUSTERED INDEX [ix_include1]
ON [Data]([Created] Desc)
INCLUDE ([ID]
       ,[Title]
       ,[Modified]);

現在這個查詢需要 16 秒，一個進化。能不能提高最多？

只要您知道它們可以破壞//性能，只要從@usr的回答（以及此處的類似問題）開始，索引視圖就可以為此工作，或者更多（準確地說是索引視圖上的覆蓋非聚集索引*）*在基礎表上，並且（根據經驗）可能會導致死鎖。INSERT``UPDATE``DELETE

如果兩個表都不是動態的，（並且假設 ID 列是唯一的），您可以創建與您的查詢具有相同條件的視圖（減去TOPand ORDER BY），在視圖的 ID 列上創建一個唯一的聚集索引，然後是一個非-‘Create DESC’ 上的聚集索引，包括 ‘Title’ 和 ‘Modified’。我創建了一個簡單的裝備來展示這一點。通過它，看看這種方法對你有用：

USE tempdb
GO

SET NOCOUNT ON
GO

/*
SELECT TOP (50) [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
 FROM dbo.[Data]
Inner Join dbo.[Permission]
   On ([Data].[ID] = [Permission].[FichaID]
  AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
       OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
       ))
Group By [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
  Order By [Created] desc
*/

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_permission_data') IS NOT NULL DROP VIEW dbo.vw_permission_data
IF OBJECT_ID('dbo.Data') IS NOT NULL DROP TABLE dbo.Data
GO

CREATE TABLE dbo.Data (
   ID                  INT NOT NULL,
   Title               VARCHAR(50) NOT NULL,
   PermID              INT NOT NULL,
   IsGroup             BIT NOT NULL,
   Created             DATETIME NOT NULL,
   Modified            DATETIME NULL,
   _ModerationStatus   INT NULL,
   Expiration          DATETIME NULL,
   Reserva             DATETIME NULL,
   FichaID             INT NOT NULL,

)
GO


IF OBJECT_ID('dbo.Permission') IS NOT NULL DROP TABLE dbo.Permission
GO

CREATE TABLE dbo.Permission (
   PermID              INT NOT NULL,  
   IsGroup             BIT NOT NULL,
   FichaID             INT NOT NULL,
)
GO

--CREATE INDEX facervo_created ON [Data] ([Created] desc);
--CREATE INDEX facervo_status ON [Data] ([_ModerationStatus]);
--CREATE INDEX facervo_expiration ON [Data] ([Expiration]);
--CREATE INDEX facervo_reserva ON [Data] ([Reserva]);
GO

ALTER TABLE dbo.Permission ADD CONSTRAINT perm_key PRIMARY KEY ( FichaID, PermID, IsGroup )
GO

CREATE NONCLUSTERED INDEX [ix_include1]
ON [Data]([Created] Desc)
INCLUDE ([ID]
       ,[Title]
       ,[Modified]);
GO

-- Add dummy data
;WITH cte AS (
SELECT TOP 206902 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
   CROSS JOIN master.sys.columns c2
   CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.Data ( ID, Title, PermID, IsGroup, Created, Modified, _ModerationStatus, Expiration, Reserva, FichaID )
SELECT rn ID, NEWID() Title, rn % 33 PermID, rn % 7 IsGroup, DATEADD( day, rn % 300, '1 Jan 2014' ) Created, NULL Modified, rn % 33 _ModerationStatus, NULL Expiration, NULL Reserva, rn FichaID
FROM cte
GO

INSERT INTO dbo.Permission ( PermID, IsGroup, FichaID )
SELECT DISTINCT 1, IsGroup, FichaID
FROM dbo.Data
   CROSS JOIN ( SELECT TOP 4 1 x FROM dbo.Data ) x
GO 

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Original Queries START
------------------------------------------------------------------------------------------------

SELECT TOP (50) [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
 FROM dbo.[Data]
Inner Join dbo.[Permission]
   On ([Data].[ID] = [Permission].[FichaID]
  AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
       OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
       ))
Group By [ID]
     ,[Title]
     ,[Modified]
     ,[Created]
Order By [Created] desc
GO

-- Original Queries END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Indexed Views START
-- with supporting non-clustered indexes
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_permission_data') IS NOT NULL DROP VIEW dbo.vw_permission_data
GO
CREATE VIEW dbo.vw_permission_data
WITH SCHEMABINDING
AS
SELECT
    [ID]
   ,[Title]
   ,[Modified]
   ,[Created]
FROM dbo.[Data]
   INNER JOIN dbo.[Permission] ON ([Data].[ID] = [Permission].[FichaID]
  AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
       OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
   ))
GO

CREATE UNIQUE CLUSTERED INDEX cdx_vw_permission_data ON dbo.vw_permission_data ( ID )
GO

CREATE INDEX idx_vw_permission_data1 ON dbo.vw_permission_data ( [Created] DESC )
INCLUDE ( Title, Modified )
GO

-- Indexed Views END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Revised Query START
------------------------------------------------------------------------------------------------

SELECT TOP 50 
    [ID]
   ,[Title]
   ,[Modified]
   ,[Created]
FROM dbo.vw_permission_data
ORDER BY Created DESC

-- Revised Query END
------------------------------------------------------------------------------------------------

Created DESC您還可以在 ( , ID)上創建一個唯一的聚集索引。索引視圖上的查詢大大減少了我的裝備中的讀取和執行時間，但我想說這兩個查詢在我的筆記型電腦上分別執行了 77 和 0 毫秒，非常快：

這些記錄的數量相對較少（200k 和 800k），所以為什麼您的查詢需要 16 秒有點神秘！？你檢查阻塞了嗎？系統很忙嗎？

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