On 5/4/05 12:16 PM, "Ian Bicking" <ianb@...> wrote:
> I don't think ActiveRecord does this either. You are asking for eager
> loading of a one-to-many join, which would mean you'd load all the
> people rows multiple times. And I guess that would be okay depending on
> your data (e.g., if most people had only one address), but it's a bit
> more aggressive than merely "eager" loading (at least how I view eager
From the example they list, it does appear to eager load one-to-many join
rows, and even multiple one-to-many join rows if desired. An example they
# Turning N+1 queries into 1
for post in Post.find(:all, :include => [ :author, :comments ])
puts "Post: " + post.title
puts "Written by: " + post.author.name
puts "Last comment on: " + post.comments.first.created_on
While in SQLObject calling post.comments implicitly causes a join, using
eager loading they've avoided that by specifying to load that in advance.
That's all I'd like to be able to replicate in SQLObject, without the excess
syntax for optimization in the FAQ on left-joins.
> As to ActiveRecord, well, beats me what they are doing. I have no idea
> what their caching looks like either, so it may be more important for
> performance in their case. It's not architecturally impossible in
> SQLObject, but I haven't needed it, and now you are doubling up
> requests, as this is not the same request as before.
My original request was to reduce my SQL queries on these fairly common
requests where I pull a table and related one-to-many join tables. Part of
the problem in my request, was that I didn't fully understand how SQLObject
caches results to reduce future lookups. I still won't claim to fully
understand this caching process, but until I can get it using a shared cache
between processes its use is limited to each request.
Maybe what I'm really requesting is more like David Cook's request to have a
tuple returned when a join or left join returns multiple related objects as
that would appear to have fetched all the objects in one query. I'm not sure
I'd want it in a tuple, but if that's a quicker intermediate solution than
somehow getting the post.comments join to check the cache first, I'd be
happy to have that for now.
> For instance, in SQLObject if you have long-lived caches (either in a
> threaded environment, or maybe some multiprocess environments depending
> how you set it up) relatively static tables will result in few queries.
> E.g, sometimes I put "status" into a table, so there's a foreign key
> that refers to the object's status, and that table has description and
> some rules and whatnot. This is efficient in SQLObject because that
> status table probably has ten fixed rows or so, and they get cached
> early and seldom need to be requeried. If ActiveRecord emitted a query
> everytime someone asked for obj.status.name, then I can see why they'd
> need eager selects.
Hmm, I think seeing more examples would help me out on some of these
optimization aspects as well. I've looked through the examples in the
distribution and found them fairly useful, is there any plans to put
examples on the website or maybe have some way for other users to contribute
their examples? (Sorry this is off topic)
> > Of course, caching can be a curse as well, especially in multiprocess
> environments. I think it would be a much more important improvement for
> SQLObject to explicitly design a caching system where, for instance, you
> could mark classes "volatile" and they'd get purged more frequently; or
> other classes "stable" and they'd stay in memory for a long time; or you
> could cache objects between processes, or maybe only database row tuples
> would be cached between processes, or whatever. I haven't done this in
> part because I don't do much multiprocess programming, and so I don't
> have an intuitive feel for what the most important aspects are; but I'd
> certainly help someone who was interested in improving that.
I'll look into some of the caching stuff Oleg helpfully pointed out, if I
wanted to have SQLObject cache to this alternate shared object caching
system, can you give me a point on where in the source I should be looking?