0

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 groups.

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, 0.

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.

  • Do you have an index on ParentId? – wezten Sep 15 '16 at 12:32
  • @wezten sure. It's a UX ParentId_Id in [Document] table. – Alex Zhukovskiy Sep 15 '16 at 13:10
  • What do you mean by 'UX'? – wezten Sep 16 '16 at 7:20
  • @wezten unique index. It's common to call non-unique index IX, unique index UX and primary key index PK. – Alex Zhukovskiy Sep 16 '16 at 12:46
  • Does that mean that there are no 2 records with the same ParentID? – wezten Sep 17 '16 at 17:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.