Assume we have following table
Id | ParentId | Timestamp | Status
We also have another table, for each
Status it shows if document can be deleted or not.
This table produces several trees (parent and its children), let's call it
Each group can be deleted then and only then each children and parent has flag
CanBeDeleted. This flag is
1 when Timestamp exceeds treshold for its status, otherwise,
Here is my query which returns correct result:
DECLARE @Thresholddate datetime2 = '2017-01-01' DECLARE @StorageId uniqueidentifier = (SELECT s.Id FROM [conf].[Storage] s) ;WITH CTE AS ( SELECT Id = di.DocumentId, GroupId = di.DocumentId, [CanBeDeleted] = 1 FROM [cleaner].[DocumentInfo] di WITH (NOLOCK) JOIN @Thresholds T ON di.StatusId = t.DocumentStatusId WHERE di.StorageId = @StorageId AND di.IsInHierarchy = 1 AND di.ReceiveDateTime < T.Threshold UNION ALL SELECT di.DocumentId, CTE.GroupId, IIF(di.ReceiveDateTime < T.Threshold, 1, 0) FROM [cleaner].[DocumentInfo] di WITH (NOLOCK) JOIN [doc].[Document] d on d.Id = di.DocumentId JOIN CTE on CTE.Id = d.ParentId JOIN @Thresholds T ON di.StatusId = t.DocumentStatusId ), FilteredCTE as ( SELECT CTE.Id, CTE.GroupId, [HierarchyCanBeDeleted] = MIN(CTE.CanBeDeleted) OVER (PARTITION BY CTE.GroupId) FROM CTE ) SELECT TOP (@BatchSize) WITH TIES FilteredCTE.Id FROM FilteredCTE WHERE HierarchyCanBeDeleted = 1 ORDER BY GroupId
But this query executes very slow. I have an execution plan, which shows that 99% of time should be spended on
index seek, but actually it takes at least one minute to get top 100 rows, how is it possble with index?..
Is it possible to enhance something? I can add extra indexes if required. For now I have PK on
DocumentID and another key for several columns:
CREATE NONCLUSTERED INDEX [IX_StatusId_StorageId_IsInHierarchy_ReceiveDateTime] ON [cleaner].[DocumentInfo] ( [StatusId] ASC, [StorageId] ASC, [IsInHierarchy] ASC, [ReceiveDateTime] ASC ) INCLUDE ( [DocumentId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Index is used indeed, it takes the same minute to take top 200 or top 200000 rows. But I'm wondering if this result could be enhanced.