Hiding a Subreport: tree

Showing posts with label tree. Show all posts

Wednesday, March 7, 2012

Hierarchical table Functions Vs Hierarchical CTE

Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)

What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

Functions can cause the plan guessing to be off big time. It is just guessing that your function is of the lowest cost possible, since if 8 happens to return no rows, it might be. If 8 returns 200 bajillion rows, it will be the other way around.

Did you do this when running the query, or just the guess?

Try running the statements with statistics io on (look up STATISTICS IO option in BOL) and see how they actually compare. They may be very close to the same size...

|||

Hi Louis,

I followed your advice on turning on the Statistics. Here is what I get when running the queries...

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT * FROM ufn_GetClientTree (9260);
GO

with ClientTree (ClientId, ParentClientId, Status, PrevStatus, Level#) AS
(
Select Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, 0
FROM Client
Where Client.ClientId = 9260
UNION ALL
Select Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, ClientTree.Level# + 1
FROM Client
INNER JOIN ClientTree On ClientTree.ClientId = Client.ParentClientId
)
SELECT * FROM ClientTree;

Here is the output I get in the messages pane...

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(14 row(s) affected)

Table '#1BC821DD'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 47 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(14 row(s) affected)

Table 'Worktable'. Scan count 16, logical reads 30626, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 75 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

Total records in the table : 15041

|||

A few more questions:

Does one actually take that much longer to execute?

Can you post the code for the UDF?

Could you run them in two batches, each with DBCC FREEPROCCACHE prior to the execution (probably not on your production server :)

These are kind of interesting results.

|||

Hi Louis,

I appreciate your interest on this topic (infact I knew you would be the one responding to this topic ;)). Here is the function and the CTE for further investigation...

Table Function:

CREATE FUNCTION [dbo].[ufn_GetClientTree] ( @.parentId AS int )
RETURNS @.tree table
(
ClientId INT NOT NULL,
ParentClientId INT NULL,
Status TINYINT NULL,
PrevStatus TINYINT NULL,
Level# INT NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int
SELECT @.lvl = 1
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT clientId, NULL, Status, PrevStatus, 0
FROM Client
WHERE clientId = @.parentId
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT ClientId, ParentclientId, Status, PrevStatus, @.lvl
FROM Client
WHERE ParentClientId = @.parentId
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, @.lvl
FROM Client
INNER JOIN @.tree AS T ON Client.ParentClientId = T.ClientId AND T.Level# = @.lvl - 1
END

RETURN;
END;

CTE:

WITH ClientTree (ClientId, ParentClientId, Status, PrevStatus, Level#) AS
(
Select Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, 0
FROM Client
WHERE Client.ClientId = 9260
UNION ALL
SELECT Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, T.Level# + 1
FROM Client
INNER JOIN ClientTree AS T On T.ClientId = Client.ParentClientId
)
SELECT * FROM ClientTree;

The total number of records in the Client table is 15052 and 14112 records of these dont have any children.

Here is the output of the function and CTE:

ClientId -- PClientId -- Sts -- PSts -- Lvl
9260 -- NULL -- 1 -- NULL -- 0
3190 -- 9260 -- 1 -- NULL -- 1
13755 -- 9260 -- 1 -- NULL -- 1
8 -- 13755 -- 1 -- NULL -- 2
6 -- 8 -- 1 -- NULL -- 3
3192 -- 8 -- 1 -- NULL -- 3
7029 -- 8 -- 1 -- NULL -- 3
123740 -- 8 -- 1 -- NULL -- 3
3 -- 6 -- 1 -- NULL -- 4
7 -- 6 -- 1 -- NULL -- 4
11 -- 7 -- 1 -- NULL -- 5
41 -- 7 -- 1 -- NULL -- 5
10684 -- 3 -- 1 -- NULL -- 5
33550 -- 3 -- 1 -- NULL -- 5

|||Itzik has a good article on this topic.
http://www.sqlmag.com/articles/index.cfm?articleid=8826|||

Is there any considerable difference in time for either?

I think that the function is hiding the costs (they tend to do that.) Try executing the code without the function wrapper to see te costs. I took this example from the Pro SQL Server 2005 code I wrote (it runs in Adventureworks)

The first execute showed a lot more reads than the second one with the function (you would think that the function was perfect!) But executing the code without the function showed small sets of reads for each level. The code in the CTE accumulated them in one bucket.

USE AdventureWorks --all of the code is to be executed
--in adventureworks

set statistics io on

--First, using 2000 style sans function
DECLARE @.managerId int
SELECT @.managerId = 140
DECLARE @.outTable table (employeeId int, managerId int, treeLevel int)

--used to hold the level of the tree we are currently at in the loop
DECLARE @.treeLevel as int
SET @.treelevel = 1

--get the top level
INSERT INTO @.outTable
SELECT employeeId, managerId, @.treelevel as treelevel
FROM HumanResources.employee as employee
WHERE (employee.managerId = @.managerId)

WHILE (1 = 1) --imitates do...until construct
BEGIN

INSERT INTO @.outTable
SELECT employee.employeeId, employee.managerId,
treelevel + 1 as treelevel
FROM HumanResources.employee as employee
JOIN @.outTable as ht
ON employee.managerId = ht.employeeId
--this where isolates a given level of the tree
WHERE EXISTS( SELECT *
FROM @.outTable AS holdTree
WHERE treelevel = @.treelevel
AND employee.managerId = holdtree.employeeId)

IF @.@.rowcount = 0
BEGIN
BREAK
END

SET @.treelevel = @.treelevel + 1
END
select *
from @.outTable
go

/* --then here is that code in a function
create function managerTree
(
@.managerId int
)
returns @.outTable table (employeeId int, managerId int, treeLevel int)
as
BEGIN
--used to hold the level of the tree we are currently at in the loop
DECLARE @.treeLevel as int
SET @.treelevel = 1

--get the top level
INSERT INTO @.outTable
SELECT employeeId, managerId, @.treelevel as treelevel
FROM HumanResources.employee as employee
WHERE (employee.managerId = @.managerId)

WHILE (1 = 1) --imitates do...until construct
BEGIN

IF @.@.rowcount = 0
BEGIN
BREAK
END

SET @.treelevel = @.treelevel + 1
END
RETURN
END
*/

--this executes that function

select *
from managerTree (140)

--recursive query using CTE

DECLARE @.managerId int
SET @.managerId = 140;

WITH EmployeeHierarchy (EmployeeId, ManagerId)
AS
(
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee as Employee
WHERE ManagerID=@.managerId

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID
FROM HumanResources.Employee as Employee
INNER JOIN EmployeeHierarchy
on Employee.ManagerID= EmployeeHierarchy.EmployeeID)

SELECT *
from EmployeeHierarchy

|||

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

;

ONLY FUNCTION CONTENT:

DECLARE @.parentId AS int
DECLARE @.tree table
(
ClientId INT NOT NULL,
ParentClientId INT NULL,
Status TINYINT NULL,
PrevStatus TINYINT NULL,
Level# INT NOT NULL
)
DECLARE @.lvl AS int

SELECT @.lvl = 1, @.parentId = 9260
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT clientId, NULL, Status, PrevStatus, 0
FROM Client
WHERE clientId = @.parentId
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT ClientId, ParentclientId, Status, PrevStatus, @.lvl
FROM Client
WHERE ParentClientId = @.parentId
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree (ClientId, ParentClientId, Status, PrevStatus, Level#)
SELECT Client.ClientId, Client.ParentClientId, Client.Status, Client.PrevStatus, @.lvl
FROM Client
INNER JOIN @.tree AS T ON Client.ParentClientId = T.ClientId AND T.Level# = @.lvl - 1
END

SELECT * FROM @.tree
;

CTE:

***************************************************************************************

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table '#52593CB8'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

Table '#52593CB8'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 4 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table '#52593CB8'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 9 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#52593CB8'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(4 row(s) affected)

Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

Table '#52593CB8'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(4 row(s) affected)

Table '#52593CB8'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(0 row(s) affected)

Table '#52593CB8'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(14 row(s) affected)

Table '#52593CB8'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

##################################################################
##################################################################

(14 row(s) affected)

Table 'Worktable'. Scan count 16, logical reads 30648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 75 ms.

Having got this information, how should I check which is better. How do i sum the logical reads. Is it simply adding all the logical reads or has some formula?

When I sum all the logical reads, I get 1707 for the 200 version, and 30924 for the CTE.

I hope i am not asking too many questions and bothering you people by still not closing this thread. Thanks for the time you have spared for this thread so far.

Hierarchical Query

I have a table representing a tree structure - self referencing table. For
any node, I need to traverse upwards until root and retreives all the
traversed nodes.
With SQL only, how could I do this? In Oracle, there is START WITH, CONNECT
BY clause to help me. What is the equivalence in sql server?Here is an alternative method
http://toponewithties.blogspot.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
>I have a table representing a tree structure - self referencing table. For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
> CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||See Itzik Ben-Gan's example
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
> I have a table representing a tree structure - self referencing table.
For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||Hi as,
Can you try the something like following which i implemented while working
on Hierarchical query (I converted this to a stored proc. This code example
is given in Books online, with little modification it worked fine for me.)
--Begin--
--
Accessing and Changing Relational Data
Expanding Hierarchies
Databases often store hierarchical information. For example, the following
data is a hierarchical representation of regions of the world. This
representation does not clearly show the structure implied by the data.
Parent Child
-- --
World Europe
World North America
Europe France
France Paris
North America United States
North America Canada
United States New York
United States Washington
New York New York City
Washington Redmond
This example is easier to interpret:
World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris
The following Transact-SQL procedure expands an encoded hierarchy to any
arbitrary depth. Although Transact-SQL supports recursion, it is more
efficient to use a temporary table as a stack to keep track of all of the
items for which processing has begun but is not complete. When processing is
complete for a particular item, it is removed from the stack. New items are
added to the stack as they are identified.
CREATE PROCEDURE expand (@.current char(20)) as
SET NOCOUNT ON
DECLARE @.level int, @.line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@.current, 1)
SELECT @.level = 1
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.current = item
FROM #stack
WHERE level = @.level
SELECT @.line = space(@.level - 1) + @.current
PRINT @.line
DELETE FROM #stack
WHERE level = @.level
AND item = @.current
INSERT #stack
SELECT child, @.level + 1
FROM hierarchy
WHERE parent = @.current
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
The input parameter (@.current) indicates the place in the hierarchy to
start. It also keeps track of the current item in the main loop.
The local variables used are @.level, which keeps track of the current level
in the hierarchy, and @.line, which is a work area used to construct the
indented line.
The SET NOCOUNT ON statement avoids cluttering the output with ROWCOUNT
messages from each SELECT.
The temporary table, #stack, is created and primed with the item identifier
of the starting point in the hierarchy, and @.level is set to match. The leve
l
column in #stack allows the same item to appear at multiple levels in the
database. Although this situation does not apply to the geographic data in
the example, it can apply in other examples.
In this example, when @.level is greater than 0, the procedure follows these
steps:
If there are any items in the stack at the current level (@.level), the
procedure chooses one and calls it @.current.
Indents the item @.level spaces, and then prints the item.
Deletes the item from the stack so it will not be processed again, and then
adds all its child items to the stack at the next level (@.level + 1). This i
s
the only place where the hierarchy table (#stack) is used.
With a conventional programming language, you would have to find each child
item and add it to the stack individually. With Transact-SQL, you can find
all child items and add them with a single statement, avoiding another neste
d
loop.
If there are child items (IF @.@.ROWCOUNT > 0), descends one level to process
them (@.level = @.level + 1); otherwise, continues processing at the current
level.
If there are no items on the stack awaiting processing at the current level,
goes back one level to see if there are any awaiting processing at the
previous level (@.level = @.level - 1). When there is no previous level, the
expansion is complete.
--End---
--
Regards,
Siva
"as" wrote:

> I have a table representing a tree structure - self referencing table. Fo
r
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH, CONNE
CT
> BY clause to help me. What is the equivalence in sql server?
>
>|||>> What is the equivalent in sql server? <<
The equivalent is a cursor becasue that what Oracle does under the
covers in their proprietary syntax. Instead:
1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
much better ways of modeling a tree.
2) Google for "Nested set model" in this newsgroup. There is no need
for procedural code.l|||Joe,
I would like to know your opinion about the approach discussed in
http://toponewithties.blogspot.com/ (Path Enumeration using Prime number
Products)
Regards
Roji
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116960769.637939.154330@.z14g2000cwz.googlegroups.com...
> The equivalent is a cursor becasue that what Oracle does under the
> covers in their proprietary syntax. Instead:
> 1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
> much better ways of modeling a tree.
> 2) Google for "Nested set model" in this newsgroup. There is no need
> for procedural code.l
>|||I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
The only problem I can see is the size of the primes as the trees get
larger, but we are living in a 64-bit world now. Since the math is
simple integer division and multiplication, the speed is probably
pretty good.
Random thought: if we give each node a prime in a general graph, then a
cycle|||Thanks Joe,
I am a happy man.
You can consider this for the next version of TREES & HIERARCHIES IN SQL :)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117037337.540731.73370@.g14g2000cwa.googlegroups.com...
>I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
> Random thought: if we give each node a prime in a general graph, then a
> cycle
>|||--CELKO-- wrote:
> I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
Kendall Willets suggested this method on comp.databases.theory some
time ago.

> Random thought: if we give each node a prime in a general graph, then a
> cycle
Prime numbers set with "divided by" binary relation is a partial order.
More specifically it is a lattice. An arbitrary DAG can be embedded
into a lattice. However, the prime numbers encoding for graphs is
volatile. Adding a node into a graph would force to recalculate
encodings for large graph fragment. Plus, how to handle acyclic graphs?|||Mikito Harakiri wrote:
> --CELKO-- wrote:
> Kendall Willets suggested this method on comp.databases.theory some
> time ago.
>
> Prime numbers set with "divided by" binary relation is a partial order.
> More specifically it is a lattice. An arbitrary DAG can be embedded
> into a lattice. However, the prime numbers encoding for graphs is
> volatile. Adding a node into a graph would force to recalculate
> encodings for large graph fragment. Plus, how to handle acyclic graphs?
Could the access path be indexed? Imagine that we don't have this silly
64 bit limit and you are presented with a node encoded with a number
3107418240490043721350750035888567930037
3460228427
2754572016194882320644051808150455634682
9671723286
7824379162728380334154710731085019195485
2900733772
4822783525742386454014691736602477652346
609
(RSA-640) and are asked what are the ancestors. Can you answer that in
a reasonable amount of time.
I'm cheating of course, if the primes are small you can factor the
number reasonably fast. Still, what about the access path? For
comparison, in case of nested sets we are talking about index range
scan (at least one way).

hiearchical list of tree

Hi,

table(id,parent,level, name)
parent ...fk referenced table(id))
level ... depth of tree (root has 0)

How SQL for hiearchical list:

name 1
name 1.1
name 1.2
name 2.
name 2.1.
name 2.1.1
name 2.1.2
etc.

?
thanks for your helpPlease post the complete DDL for your table, some sample data, and the desired result. That, I might be able to help with.|||create table A(
id int primary key not null,
parent int null, --parent of the row
level int null, --position in DB tree from root
name varchar (20)
)

alter table A add
constraint fk foreign key(parent) references A(id)

insert into A(id,parent,level,name) values (1,null,0,'name 1.');
insert into A(id,parent,level,name) values (2,1,1,'name 1.1.');
insert into A(id,parent,level,name) values (3,1,1,'name 1.3.');
insert into A(id,parent,level,name) values (4,2,2,'name 1.1.1.');
insert into A(id,parent,level,name) values (5,1,1,'name 1.2.');
insert into A(id,parent,level,name) values (6,3,2,'name 1.3.1.');
...

I'd like to display :
name 1.
name 1.1.1.
name 1.2.
name 1.3.
name 1.3.1.
...

name 1.
name 1.|||this will do it...select name from A order by name
i know it's not the general solution, but it is a prefectly good solution to the particular data that you have, with the enumerated path included as part of the name|||thanks, but else, not order name
Column a.name contains 'sdfdasasd' etc too.|||okay, see Recursive Queries in SQL Server 2005 (http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp)|||I'd like to avoid a recursion solution. How ways are possible?
DDL can be altered.|||how deep is the tree? how many levels?|||more than 15 levels|||my condolences, as that would be phenomenally difficult for most users to navigate|||I'd like to avoid a recursion solution.
Why? Recursion would be the easiest method, especially if the number of levels isn't fixed.|||because a recursion should be time difficult|||you may not want to hear this, but any solution for the adjacency model going down 15+ levels will be "time difficult"

have you thought about a different data design?

do a search for joe celko's nested set model

Wednesday, March 7, 2012

Hierarchical table Functions Vs Hierarchical CTE

Hierarchical Query

hiearchical list of tree

Hiding a Subreport

Blog Archive

About Me