Re: Relational/hierarchical data problem

> Contrast this with the approach mentioned in my last post,
> which allows me to execute a single SQL statement
> to produce the same report in under a second.

Bill, comparing the time (1.6s) to generate the report from normalized data (aidb solution) versus the time (1s) to execute the one-line query using a pre-prepared, non-normalized IndexTable is not an equivalent comparison because the one-line query method does not include the time to index/prepare the IndexTable?

How exactly did you determine it took 1 second to execute the one-line query? Mentally? Handwatch? Programmatically?

Have you actually created the IndexTable for all 41 goats? How long did that take?
Have you actually run that one-line query against actual data supplemented by the IndexTable?
If the herd produces one offspring, approximately how long would it take to update the IndexTable? Would it take less than 1.6 seconds?

In addition, the one-line query

"SELECT g1.ancestorID FROM
((SELECT ancestorID FROM IndexTable WHERE probandID = [Proband1]) as g1 INNER JOIN
(SELECT ancestorID FROM IndexTable WHERE probandID = [Proband2]) as g2 ON g1.ancestorID = g2.ancestorID) ORDER BY g1.ancestorID"

does not produce an equivalent report.
First, it does not report the distance between goats. Second, as you stated earlier, it only
"determine[s] shared ancestors for [just] 2 individuals".

The aidb generated report is not for just 2 goats. The aidb report is not for just 1 goat and the remaining 199 goats. The aidb report is for all 200 goats with respect to every other remaining goat in the herd generated in 1.6 seconds!

--
Posted via http://dbforums.com