Solved

SQL Tree

Posted on 2013-05-26
8
454 Views
Last Modified: 2013-05-27
I am working with T-SQL and read that CTE is better than subqeries when working with Trees.  I am not familiar with the term Tree with regards to T-SQL.  Any ideas on what this is and how it is used in T-SQL ? Thanks for any feedback.
0
Comment
Question by:fjkaykr11
8 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 39198574
the term 'tree' is sometimes used as a way to describe a hierarchical query, in TSQL these typically require "recursion" and CTEs are a good method for this.

refer to this article perhaps:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2440-MS-SQL-2005-T-SQL-Techniques-Logical-BOM-Explosion.html
a CTE approach is discsssed there (referring to a BOM which is a hierarchy or 'tree')
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 225 total points
ID: 39198644
You can use the following methodologies to represent hierarchies:

> Nested Set
> Materialized Path
> Adjacency

None of the above require recursion.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 225 total points
ID: 39198898
If your tree has a high number of levels, recursion is to be avoided to prevent excessive locking.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 39198930
opinions differ (on almost all sql topics :), e.g.
... the simplicity of adjacency list implementation and management, make adjacency list a preferred way to store hierarchical data in SQL Server versions that support recursive CTEs, that is versions from 2005 onwards.
(emphasis added), see http://explainextended.com/2009/09/25/adjacency-list-vs-nested-sets-sql-server/

the article given earlier also presents alternative approaches.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 16

Accepted Solution

by:
Surendra Nath earned 225 total points
ID: 39199165
Hi There

To simplify the things over here, the SQL Tree is nothing different from a family tree, that we all are most familiar with.... A parent will have children.. grand children.. grand grand childrens......

in the a tree the parent is called the parent node and the young generation of the family is called the leaf node.

Now, how this tree can be applied in T-SQL...
Let us assume you have a table named schema as below

Employee_ID
Employee_Name
Reporting_manager

now the data in the table can be as below

Employee_ID    Employee_Name    Reporting_manager
1                           FRED                          none
2                           Surendra                   FRED
3                           Neo                             FRED
4                           Jarvis                          Surendra

now, if we want to know the reporting hierarchy for the employees... then this is how it will be

Surendra  & Neo will be directly reporting to FRED, and Jarvis will be reporting to Surendra who inturn reports to Fred.

This is a organizational tree, if we need to find out who reports to who kind of quries, it can be done using recursion using CTE or usual subqueries or Cursors...
But the fast apporach is with CTE.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 225 total points
ID: 39199204
<<opinions differ (on almost all sql topics :), >>
Fair enough but looking at the example provided, the OP only shows he does not know how to implement nested sets on SQL Server and blames poor performance not realizing he does not understand how to implement them:

> First, he does not understand that right/left are to be on the same table for browsing a specific hierarchy.  He uses an example of a JOIN operation between 2 hierarchies to conclude for poor performance.  Typically, the left and right sides on nested set are columns not tables.
> Second as a consequence of the above, he uses a JOIN operation to justify poor performance
> Third, on a nested set representation, leafs and ancestors are obtained  according the difference between right and left values: all descendants are obtained according to the fact that the difference between the rich and left is always 1.  All ancestors are obtained as having the largest differences between right and left values AND including a specific value.  In other words, the OP says he is using nested set when he is not.

For what it is worth, I have above 10 level deep data warehouses running BI hierarchies returning real time grouped aggregates to report users and the slowest query I have is one second.  This is simply impossible using adjacency lists.

I recommend nested sets when browsing, aggregation and tree depth become important and adjacency lists in other cases because they are easier to implement.

Hope this helps.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 225 total points
ID: 39199213
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 39199573
Great responses.   @Racimo and @NeoJarvis -- thank you for providing specific examples of what I am looking to grasp.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now