Just following on from @usr's answer (and similar question here), indexed views could work for this, or more (precisely a covering non-clustered index on the index view), as long as you're aware they can destroy INSERT / UPDATE / DELETE performance on the underlying tables and (from experience) can contribute to deadlocks.
If neither table is that dynamic though, (and assuming the ID column is unique), you could create view which has the same criteria as your query (minus the TOP and ORDER BY), create a unique clustered index on the view's ID column, then a non-clustered index on 'Create DESC', including 'Title' and 'Modified'. I've created a simple rig to demonstrate this. Work through it and see this approach could work for you:
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
------------------------------------------------------------------------------------------------
You could also create a single unique clustered index on ( Created DESC, ID ). The query on the indexed view dramatically reduced the reads and runtime in my rig, but I would say both queries were running 77 and 0 milliseconds respectively on my laptop, so really quick:
These are relatively low numbers of records (200k and 800k), so it's a bit of a mystery as to why your query is taking 16 seconds!? Have you checked for blocking? Is the system very busy?
