Information at link.

I’ll write this down so I won’t forget it. This common table expression enables you to omit CURSORS and apparently it goes quicker.

This query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

INSERT #TempTreeStructures (TreeStructureId) VALUES (@RootTreeStructureId)
 
SET @RootRightValue = @RootRightValue - 1
 
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RootLeftValue + 1
 
WHILE (@RightValue < @RootRightValue)
BEGIN
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RightValue + 1
END
 
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
tn.Description,
tn.DateChanged ,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId = (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures)
AND tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)
UNION ALL
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
NULL AS [Description],
NULL AS DateChanged,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId IN (SELECT TreeStructureId FROM #TempTreeStructures) AND
ts.TreeStructureId <> (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures) AND
tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)

could be replaced by this one

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

;WITH TreeData (TreeStructureId, LeftValue, RightValue, TreeId)
AS (
SELECT TOP 1
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
WHERE TreeStructures.TreeId = @TreeId
AND TreeStructures.LeftValue = @RootLeft + 1
UNION ALL
SELECT
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
INNER JOIN TreeData ON TreeStructures.LeftValue = TreeData.RightValue + 1
WHERE TreeStructures.TreeId = TreeData.TreeId
)
SELECT TreeStructures.TreeStructureId,
TreeStructures.LeftValue,
TreeStructures.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA ,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeStructures
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeStructures.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeStructures.TreeStructureId
ORDER BY DateChanged DESC
)
AND TreeStructures.TreeStructureId = @RootTreeStructureId
UNION ALL
SELECT TreeData.TreeStructureId,
TreeData.LeftValue,
TreeData.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeData
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeData.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeData.TreeStructureId
ORDER BY DateChanged DESC
)
ORDER BY LeftValue
;

I’ll have to read some more on it to fully understand its capabilities.

thx, Lieven Cardoen

pixel WITH common table expression (Transact SQL)
No TweetBacks yet. (Be the first to Tweet this post)