Forum OpenACS Q&A: db_dfs - simulating "connect by" queries

Collapse
1: db_dfs - simulating "connect by" queries
Posted by Mark Dettinger on 11/05/00 12:14 AM
I've written a new DB API function db_dfs (depth-first search) that
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.
Collapse
2: Response to db_dfs - simulating "connect by" queries (response to 1)
Posted by Dan Wickstrom on 11/06/00 01:47 PM
We already do recursion in pl/pgsql functions now to emulate connect-by statements, and it's quite slow.  I would have to assume that moving the recursion to the tcl layer would be even slower, so I don't think there would be of any benefit in doing the connect-by emulation this way.
Collapse
3: Response to db_dfs - simulating "connect by" queries (response to 1)
Posted by Mark Dettinger on 11/08/00 06:43 PM
My primary goal was not to improve performance. After all, currently the classic ACS does not work at all for Postgres, so you have to change (among other things) all "connect by" queries to something else.
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?

Collapse
4: Response to db_dfs - simulating "connect by" queries (response to 1)
Posted by Dan Wickstrom on 11/08/00 07:17 PM
I'm not being critical, I just think that in practice we would be
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
db?