From: Robert Klemme <shortcutter@...>
Date: 2009-09-15T21:44:41+09:00
Subject: Re: Pre-allocate large amount of memory?

2009/9/15 Carsten Gehling <carsten@sarum.dk>:
> Robert Klemme wrote:
>
>> So you do actually allow for loops, i.e. company A owns 10% of company
>> B which owns 10% of company A.
>
> Yes that is allowed (we define some rules in the extraction code to
> avoid infinite recursion).

Good.

>> If I understand that table design properly it is awful because
>> semantics of columns one, three and four change based on content of
>> column two.  The usual way would be to model this with fixed
>> semantics, i.e. only have one direction of ownership relation in the
>> table.  In your case you will probably have to do a normalization step
>> by defining a view on this table with a UNION ALL or use a WITH clause
>> in the query to ensure the query can be built in a reasonable way.
>
> Actually the above structure is a view, which adds this direction. The
> real data-table only holds one record each relation, that is:
>
> "BasCorp", "ownedby", "BarCorp", "45%"
> "RteCorp", "ownedby", "QweCorp", "20%"

Oh.

>> There are features in modern RDBMS which allow for recursive querying.
>>  In PostgreSQL and Microsoft SQL Server you can use WITH expression:
>> http://www.postgresql.org/docs/8.4/static/queries-with.html
>> http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx
>
> I am using Microsoft SQL Server, but I didn't know about recursive
> querying.
>
>> The downside is that recursive queries tend to have a performance hit
>
> Perhaps the best way will be going with my current setup (i.e. loading
> the entire data). But use recursive querying to reload part of the data
> when relations are changed.

Before you do that: I would first try out the query and see how well
it performs with your data.  If not, then maybe additional indexing
may help.  Only if that also fails _then_ I would switch to a caching
approach.

The reason: I would try to keep architecture simple and avoid
redundant data storage as this tends to cause issues.  If you can get
the data you need just in time via a reasonable efficient query then
this is much more preferable to your big caching process approach.

>> The nested set model (Josh mentioned it as well) might help although I
>> haven't thought through all implications in your case:
>
> I would rather not begin to alter my data structure.

Why?

>> You still have the issue that you maintain redundant data and must
>> find a way to invalidate your cache when the base data changes.
>
> I think that I may have found a way to calculate, exactly how many
> records I need to reload, if the relation between two companies are
> added/changed/deleted. And SQL Sever's WITH option might help me there.
>
> I will have a look at that now. Thanks a bunch for your input - all of
> you.
>
> I will post my results, when I get there.

We'll be waiting eagerly. :-)

Cheers

robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/