Forum OpenACS Q&A: db_dfs - simulating "connect by" queries
works like db_foreach and simulates a hierarchical query by a
recursive algorithm, without using the Oracle-specific "connect by"
feature. See http://dev.arsdigita.com/connect_by.tcl for details. I
welcome any feedback.
If all hierarchical searches were implemented with db_dfs, then they would at least work by default, and only those searches that turn out to be too slow would have to be changed.
To quantify the difference: According to a series of tests I have run, the simulation of "connect by" by recursion in the Tcl layer is 4-5 times slower than the original Oracle feature.
I don't know how slow the emulation is in PL/SQL. Has somebody benchmarked it?
better off porting all of the connect-by statements from the start.
Even with recursion in the db, we've had several complaints about slow
queries that were related to the ported connect-by statements. If we
had something like db_dfs, the temptation would be to not port the
connect-by queries until they became a problem.
The plus side to using db_dfs is that we get a common interface for
fixing the connect-by queries. Since it's possible to do dynamic sql
inside of pltcl functions, I wonder if it wouldn't be possible to
combine db_dfs with a pltcl function that did the recursion inside the