--0016364ee8d46384000473aa2096
Content-Type: text/plain; charset=ISO-8859-1

My 2 cents

I have used this kind of caching method in past and works really well for
me. I was able to speed up a database-with-very-heavy-nested-data
to csv script by more than 30x and this despite the fact that I had analyzed
all queries and had all necessary indexes in place.
So I guess atleast it works if the amount of data is not very big. I
guess(and I can be severely wrong) that can also be overcome by using
something like localmemcache gem which can be used to dump this data on an
arbitrary memory location which is not handled by ruby process.

Piyush

On Tue, Sep 15, 2009 at 6:14 PM, Robert Klemme
<shortcutter / googlemail.com>wrote:

> 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/
>
>

--0016364ee8d46384000473aa2096--