http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660See the name he posted under: [ayende.com/.../...lect-is-broken-or-just-slow.aspx](http://ayende.com/Blog/archive/2008/10/16/when-select-is-broken-or-just-slow.aspx#26475)http://ayende.com/3654/when-select-is-broken-or-just-slow#comment9http://ayende.com/3654/when-select-is-broken-or-just-slow#comment9Sat, 18 Oct 2008 10:40:18 GMTI am missing a point .. who is "Fool" here ? http://ayende.com/3654/when-select-is-broken-or-just-slow#comment8http://ayende.com/3654/when-select-is-broken-or-just-slow#comment8Sat, 18 Oct 2008 06:52:07 GMTFool, There is one problem with any of your suggestions. It assume that the hierarchy is fixed and unaffected by business logic, which is not the case in my scenario http://ayende.com/3654/when-select-is-broken-or-just-slow#comment7http://ayende.com/3654/when-select-is-broken-or-just-slow#comment7Fri, 17 Oct 2008 21:58:14 GMTmeisinger, Traversing in memory trees is _fast_ http://ayende.com/3654/when-select-is-broken-or-just-slow#comment6http://ayende.com/3654/when-select-is-broken-or-just-slow#comment6Fri, 17 Oct 2008 21:56:30 GMTYou forget the cases when select is the right tool for the job but simply won´t work as expected because the database engine is trying the wrong execution path. I had a case like that with Ms Sql 2000 and the developers preferred to ignore it because the "engine knows more" and working at that level is not "a good practice" (it appears than getting broken batch reports because Sql Server was choking in data IS a good practice). http://ayende.com/3654/when-select-is-broken-or-just-slow#comment5http://ayende.com/3654/when-select-is-broken-or-just-slow#comment5Fri, 17 Oct 2008 18:13:01 GMTAndrey, Those are just ways to get the data from hierarchical structure in the rows. As a simple example, you want to filter the tree, and upgrade children of filtered nodes to the next top level. You can't really do this with SQL. http://ayende.com/3654/when-select-is-broken-or-just-slow#comment4http://ayende.com/3654/when-select-is-broken-or-just-slow#comment4Fri, 17 Oct 2008 08:43:05 GMT__The most natural way of working with trees is with recursion, and SQL is just not the right way of dealing with it. CTEs in SQL2005/DB2, hierarchyid in SQL2008, Connect By in Oracle. I do agree with your main point, however. http://ayende.com/3654/when-select-is-broken-or-just-slow#comment3http://ayende.com/3654/when-select-is-broken-or-just-slow#comment3Fri, 17 Oct 2008 06:38:05 GMTThis is great if you can keep your entire tree in memory. There are other approaches that will work with nearly equal performance as keeping the tree in memory but scale much better. One now aging approach is one that Joe Celko called nested sets. We used this at my last company. It has been documented a number of places, one of which is this codeproject article: [www.codeproject.com/KB/database/nestedsets.aspx](http://www.codeproject.com/KB/database/nestedsets.aspx) Another approach (of my own design) is keeping another table that I call a decendency list. The cool thing with this approach is that it's very narrow and maintaining that left-right thing that slows down inserts and moves within the nested-set approach really isn't an issue. The admittedly bad thing is the need for a second table to hold the hierarchy relationships for efficient queries. Another approach is a recursive query feature that was built into SQL Server 2005 that has performance on par with the above structured approaches. This is cool because the natural parent-child relationships remain in the intuitive just-the-parent-id-in-child-record approach. The feature is called CTE (Common-Table-Expression). [msdn.microsoft.com/en-us/library/ms175972.aspx](http://msdn.microsoft.com/en-us/library/ms175972.aspx) [blog.crowe.co.nz/.../...E-Example-of-a-simple.aspx](http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx) Oracle has something similar with their "connect by" clause... [asktom.oracle.com/.../f](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53139879276132) Not using the above? I guess you're on your own. Do some research and find something that works for your situation. ...or load the tree in memory at the start of your app. Your call. :) http://ayende.com/3654/when-select-is-broken-or-just-slow#comment2http://ayende.com/3654/when-select-is-broken-or-just-slow#comment2Thu, 16 Oct 2008 16:37:59 GMThm... perhaps i am missing something this tree would be a right only tree correct? wouldn't traversing this tree take a really (really) long? http://ayende.com/3654/when-select-is-broken-or-just-slow#comment1http://ayende.com/3654/when-select-is-broken-or-just-slow#comment1Thu, 16 Oct 2008 15:52:50 GMT