Query optimization of tree hierarchical data in SQL

In a data query, starting with 2008, SQL Server provides a new data type, HierarchyID, specifically for manipulating hierarchical data structures.

The HierarchyID type logically encodes information about a single node in the hierarchy tree by encoding the path from the root of the tree to the node.

This path is logically represented as a sequence of node labels for all children that are accessed after the root. The representation begins with a slash, and the path that accesses the root only is represented by a single slash. For levels below the root, each label is encoded as a sequence of integers separated by points. The comparison between the children is to compare the sequence of integers separated by points in dictionary order. A slash follows each level. Therefore, the slash separates the parent from its children. For example, the following are valid HierarchyID paths with lengths of 1, 2, 2, 3, and 3, respectively:

? /

? /1/

? /0.3.-7/

? /1/3/

? /0.1/0.2/

In the days when there was no hierarchyid, we queried the parent and all the subordinates in the way of the CTE, but the way the CTE would be very slow in the case of a lot of data, we later built the path to speed it up. Then, with the HierarchyID type, it's natural to use HierarchyID.

Now, let's look at the power of HierarchyID by a practical example.

One: CTE method

With Ctegetchild as
(
SELECT * from el_organization.organization WHERE id= ' ecc43c7159924dca91e2916368f923f4 '--and [state]=0 and AuditState=2
UNION All
(
SELECT a.* from El_organization.organization as A
INNER JOIN Ctegetchild as B on A.parentid=b.id--and a.[state]=0 and a.auditstate=2
)
)

query out 4489 lines, need 25S.

It seems that the CTE has reached a point where it is intolerable, so now we use it to optimize.

Two: HierarchyID

First, we need to create the field and assign it a value.

Create function F_cidname (@id varchar) returns varchar (max) as
Begin
declare @pids nvarchar (max);
declare @pNames nvarchar (max);
Set @pids = ';
Set @pNames = ';
With CTE as
(select Id,parentid,name from el_organization.organization where ID [email protected] ' 00037fdf184e48d084b87c3499e3c0e5 '
UNION ALL
Select B.id,b.parentid,b.name from CTE A, el_organization.organization b where A.parentid = b.ID
)

Select @pids =convert (varchar), convert (int, convert (varbinary (max), id)) + '/' + @pids from CTE
Return '/' [email protected]

End
Go

Next, we need to update the entire table:

UPDATE el_organization.organization SET pids=dbo.f_cidname (ID)

Note that the ID is a 32-bit string of GUIDs, and the HierarchyID field does not support the path within that size, so we convert the GUID to integer: convert (varchar), convert (int, convert ( varbinary (max), id)))

2.1 TIP

Exception Message:DataReader.GetFieldType (4) returned null. Exception data:System.Collections.ListDictionaryInternal

Note that it is very likely that we have updated the field and our program has gone wrong, as above. This time, we need to put

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

This DLL is packaged into our application. The reason is not explained.

Look at the effect, the modified code is:

DECLARE @tmpIds HierarchyID
SELECT @tmpIds =pids from el_organization.organization WHERE id= ' ecc43c7159924dca91e2916368f923f4 ';
With Ctegetchild as (
SELECT * from el_organization.organization WHERE id= ' Ecc43c7159924dca91e2916368f923f4 '
UNION All (
SELECT * from El_organization.organization WHERE pids.isdescendantof (@tmpIds) =1
)
)
SELECT * from Ctegetchild

Now, our time is within 1S.

2.2 Everything in order to not move the application layer code

Now, since we have added a field, we need to maintain this field, such as: This record is moved to a different parent in the application, and this field needs to be updated. In order to not move the upper code, the only thing to do is to create a trigger, namely: The original ParentID changes, you need to update the PIDs field, so we create a trigger as follows:

Create Trigger Updateorgpids
On El_organization.organization
After update
As
If update ([ParentID])
Begin
DECLARE @tmpId varchar (36)
Select @tmpId =id from inserted
Update el_organization.organization set Pids=dbo.f_cidname (@tmpId)
End
Go
--Drop Trigger El_organization.updateorgpids

Sweep, follow the most lessons, get a daily workout

Query optimization of tree hierarchical data in SQL