I really thought I'd love the use of the hierarchyid type... but things are slipping for me.. sorry if I am hijacking this a bit, but it's relevant.
I am finding it quicker to do a flattened view using ParentId -> Id than Node.GetAncestor(1) = Node
Is there a reason? :blink:
I've got a million rows in a catalgoue table that looks like this:
CREATE TABLE [dbo].[tblHierarchy](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[Node] [hierarchyid] NOT NULL,
[ParentNode] AS ([Node].[GetAncestor]((1))) PERSISTED,
[NodeLevel] AS ([Node].[GetLevel]()) PERSISTED,
[Code] [nvarchar](64) NOT NULL,
[TypeId] [int] NOT NULL,
[TargetId] [int] NULL,
[CreateDate] [datetime] NOT NULL,
[CreateUser] [nvarchar](32) NOT NULL,
[UpdateDate] [datetime] NOT NULL,
[UpdateUser] [nvarchar](32) NOT NULL,
[UpdateCode] [nchar](1) NOT NULL,
CONSTRAINT [tblHierarchy_PK] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
) ON [PRIMARY],
CONSTRAINT [tblHierarchy_AK1] UNIQUE CLUSTERED
(
[Node] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Simples... I've had lots of different indexes on, but I've stripped them off to get back to basics.
The tree I am querying has 21000 records.
If I do the following:
SELECT
[H1Id] = h1.Id
,[H1Code] = H1.Code
,[H1Type] = H1.TypeId
,[H2Grp] = H2.Id
,[H2Code] = H2.Code
,[H2Type] = H2.TypeId
,[H3Grp] = H3.Id
,[H3Code] = H3.Code
,[H3Type] = H3.TypeId
,[H4Grp] = H4.Id
,[H4Code] = H4.Code
,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
ON H0.Id = H1.ParentId
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H1.Id = H2.ParentId
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H2.Id = H3.ParentId
INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
ON H3.Id = H4.ParentId
WHERE H0.Code = 'ROOT'
AND H1.TypeId = 1
Compared to:
SELECT
[H1Id] = h1.Id
,[H1Code] = H1.Code
,[H1Type] = H1.TypeId
,[H2Grp] = H2.Id
,[H2Code] = H2.Code
,[H2Type] = H2.TypeId
,[H3Grp] = H3.Id
,[H3Code] = H3.Code
,[H3Type] = H3.TypeId
,[H4Grp] = H4.Id
,[H4Code] = H4.Code
,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
ON H0.Node = H1.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H1.Node = H2.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H2.Node = H3.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
ON H3.Node = H4.Node.GetAncestor(1)
WHERE H0.Code = 'ROOT'
AND H1.TypeId = 1
The first query runs shed twice as fast. Even if I substitute Node.GetAncestor(1) with my persisted computed column of ParentNode.
It's important for the dataset to be flattened for exporting to other systems. If someone reading this has better plans please reply or PM me. would be grateful as always.
I guess we may need both approaches? It's incredibly powerful, but it has it's limits?
Kev