Re: parent/child relationship in the same table.

In article <a6e74506.0111172157.3fce3c6e_at_posting.google.com>, James says...
>
>> >Here is the psuedo code:
>> >Call A(0)
>> >Function A(par)
>> > rs = db.execute("Select * from T_ParChild Where ParId = " & par)
>> > While (!rs.EOF)
>> > child = rs("Child")
>> > Print par, child, rs("Data")
>> > Call A(child)
>> > Loop
>> >End
>> >
>> >An oodb can perform the above much faster than relational dbs. In a
>> >relational db, the time to resolve the relationships increases with
>> >the number of records. In an oodb, the resolution time is not only
>> >quicker but constant regardless of number of "records".
>
>> Object folks thinking is frozen in terms of nested loops
>> that user has to write.
>If you pulled a marble out of a bag and it was black, you would say
>that the remaining marbles in the bag are black. What are your chances
>of being correct?

Well, but if those folks spend most of their time arguing whether it is safe to inherit circle from ellipse, you wouldn't expect innovative query optimization technique coming from that camp, right?

>The solution was presented using standard SQL and programming
>techniques, which I believe will require nested loops? Can you provide
>an alternate practical solution using standard SQL and programming
>techniques?

You call SQL from a function that you invoke recursively. That's just one and not very efficient possibility: essentially, you coded some execution plan for recursive SQL. Note, that if some incremental evaluation system is in place (for example, materialized paths, Joe Celko nested sets, etc), the optimiser can choose a different plan.

>> Not quite.
>Note quite what?
>1. oodb is faster at resolving parent/child relationships?
>2. resolution time in rdb increases with # of records?
>3. resolution time in oodb is constant regardless of # of "recs"?

What is "resolution time" may I ask?

OODB is losely defined entity to draw any conclusion what database is faster today. Given that RDB has some flexibility in choosing the access path to the data, many people believe that RDBMS are faster. Even on unconventional topics like calculating transitive closure.

>> A relational database has a concept of "pipelining":
>> it could answer the query above in a single pass through the table.
>What exactly is "pipelining"?
>Which databases is "pipelining" available in?
>Does "pipelining" internally not use any nested loops?
>Does "pipelining" disproves any of the 3 points above?
>How do I use "pipelining" to create the desired solution?
>
>Would you like to benchmark "pipelining" with XDb which resolves
>parent/child relationships at a rate of 100 mil/sec on a 800Mhz PC?

So what? If one can cook some one-user in-memory "database", no doubt, there would be a benchmark where it rocks. Received on Mon Nov 19 2001 - 03:51:39 CET