Relational/hierarchical data problem

I'm working with data similar to that found in a family tree. In this case it applies to animals and particularly breeding. The data is held within a single table in a relational database and takes the form of a single record for each animal, which includes two fields to reference the unique ID of both the mother and the father.

I need to determine how many shared ancestors exist between the subject and any other animal and which ancestors are shared.

I have already created a graphical "family tree" for any given animal. This recursively retrieves a single record from the table, gets the id for the mother and father and repeats the cycle for the required number of generations, building a collection of ancestor records as it goes. Performance is acceptable for this task.

At present I am using the "family tree" approach to trace shared ancestors between animals. This is not a problem for two animals, but if the process is repeated for an entire herd performance begins to take large hits. The problem is that shared ancestry must be checked for the entire herd in order to determine which are the best breeding candidates for any given animal, so I don't have any way to avoid this task.

I have played around with a few alternative approaches including:

1. temporary database table to hold ancestor records for every animal in the herd. There are constant maintenance issues here to ensure that the data stays current.
2. XML/hierarchical storage for the data. Cross breeding across generations makes this a nightmare.

So far I have found nothing that will improve on performance. Is this a common problem with a "best practice" solution, or can anyone suggest how I could improve efficiency here?

Regards

Will Received on Tue Feb 18 2003 - 07:52:46 CST