2

I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to the mix; then I could index that and hopefully make some of the queries faster.

This query is modeled on the msdocs sample on hierarchy id. Before I ran it, I put indexes on (employeeid), (managerid, include employeeid), and (officeid, managerid, employeeid) hoping to make it as fast as possible. But the query took over 5 hours to run.

I looked at the plan, and the (officeid, managerid, employeeid) index is used for both parts of the cte.

What I don't understand is that the ROW_NUMBER() windowing function also appears to have added a SORT node, which ss says is taking 94% of the query time. It's already using an index that is pre-ordered in the same way, so why is there a SORT node taking up 94% of the time?

enter image description here

Thanks

WITH paths(path, duns, extended_gu, extended_parent)   
AS (  -- This section provides the value for the root of the hierarchy  
    SELECT hierarchyid::GetRoot() AS path, employeeid, officeid, managerid
    FROM OrgChart where officeid = 0

    -- This section provides values for all nodes except the root  
    UNION ALL   
    SELECT CAST(p.path.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY RD.officeid, RD.managerid 
    ORDER BY RD.officeid, RD.managerid) AS varchar(30)) + '/' AS hierarchyid),   
    RD.employeeid, RD.managerid, Rd.officeid
    FROM OrgChart rd   
    JOIN paths AS p ON rd.managerid = P.employeeid
) 
UPDATE rd set tree=P.Path
FROM Paths P
INNER JOIN  OrgChart rd on rd.duns = P.Duns

EDIT: Schema, added in response to comment below. I don't see a "Paste The Plan" as a feature in the editor; where is that? Rather than null on the roots, those ids are 0

CREATE TABLE [dbo].[OrgChart](
    [employeeid] [int] NOT NULL,
    [managerid] [int] NOT NULL,
    [officeid] [int] NOT NULL,
    ...
    [tree] [hierarchyid] NULL
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_employeeids] ON [dbo].[OrgChart]
(
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_officemgremployee] ON [dbo].[OrgChart]
(
    [officeid] ASC,
    [managerid] ASC,
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [uixOrgChart_mgremp] ON [dbo].[OrgChart]
(
    [managerid] ASC
)
INCLUDE([employeeid]) 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]
9
  • 2
    Hi! Welcome to dba.SE. To better answer your question, please provide the table definitions, and use something like Paste The Plan instead of pictures. Also see: dba.stackexchange.com/help/minimal-reproducible-example and spaghettidba.com/2015/04/24/…
    – user212533
    Feb 18, 2021 at 16:29
  • 1
    Pate The Plan is a separate site. Upload your plan XML there and add the link to your question.
    – Dan Guzman
    Feb 18, 2021 at 17:12
  • 1
    Can you explain what you are trying to do ? Why do you need a rownumber ? Couldn't you add it to the table before you do the recursive CTE ? Itzik has a similar example but he has ... FROM PATHS JOIN OrgChart ... sqlperformance.com/2020/09/t-sql-queries/… Feb 18, 2021 at 17:18
  • Thanks for all the replies... The ROW_NUMBER() was just how the MS sample code for learning about the hierarchyid assigned tier level values for siblings when creating a hierarchyid data type for a tree structure. I've never worked with the hierarchyid type before, but I thought if I could create one and then index it, that it would make "how many children are under this whole tree?" type queries faster. But so far, it's pretty expensive to construct given the data as presented. Feb 18, 2021 at 18:45
  • 1

1 Answer 1

0

Actually Erik's comment above was the most probative.

Creating a better index cut the time from 5 hours to a little over 3.

The other thing that made an even bigger difference was not updating columns on the main table; rather, I directed the hierarchy ids off into a normalized table instead. That cut the time from a little over 3 to a little over 1

1
  • If 1 hour is sufficient for what you need, then you're good to go, woohoo! Otherwise, an FYI that I'm sure it's possible to cut that down to as little as a few seconds if not sub-second. (I'm not sure the size of your tables, but I've done recursive CTEs over hundreds of thousands of records in sub-second time, on modest hardware.) But doing so would require your current actual execution plan to be attached to your post, such as through Paste The Plan.
    – J.D.
    Mar 5, 2021 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.