3 ms·
Don't CTEs solve the problem of representing trees in a DB (modulo depth limit)? I have used them to construct schemas that represented org charts and other kin
by noblethrasher 12y ago
Don't CTEs solve the problem of representing trees in a DB (modulo depth limit)? I have used them to construct schemas that represented org charts and other kind of trees. Right now I'm working on a project that will probably use an RDBMS to store the parse tree of a compilation.
- bitL 12y agoTry to benchmark your CTE query against a nested-sets implementation as described e.g. here: http://www.evanpetersen.com/item/nested-sets.html http://www.evanpetersen.com/item/nested-sets.html
- mason55 12y agoI can't speak for any other server but Postgres recurvise CTEs should be reasonably performant
- bitL 12y agoI am not sure what is the situation today; in 2005 when I wrote nested-sets implementation for TSQL and benchmarked it against Oracle's START-CONNECT extension it was a few milliseconds vs seconds for large hierarchies. Of course, nested sets need an occasional rebuild of the whole hierarchy so they are only useful for mostly static hierarchical data. If you have only a couple of levels, you are probably fine with what your SQL server offers; for huge amount of hierarchical data you need to use something smarter.