undefined | Better HN

0 pointsnoblethrasher12y ago0 comments

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.

0 comments

3 comments · 2 top-level

mason5512y ago· 1 in thread

I can't speak for any other server but Postgres recurvise CTEs should be reasonably performant

bitL12y ago

I 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.

bitL12y ago

Try to benchmark your CTE query against a nested-sets implementation as described e.g. here:

http://www.evanpetersen.com/item/nested-sets.html

j / k navigate · click thread line to collapse