How do you optimize Castle ActiveRecord calls

Question

How do you optimize ActiveRecord calls in your ASP.NET MVC 2 web applications ?

I'm sitting in front of my project and all is fine until I start to fill in data. Like a lot of projects I have a data structure similar to this:

A planet has many countries. A country has many states/provinces. A State has many cities. A city has many neighborhoods.

Below an example of Castle ActiveRecord/NHibernate persistent business object

[ActiveRecord]
public class Country {

    [Property]
    public String CountryName { get; set; }

    [HasMany(typeof(States))]
    public IList<State> States { get; set; }
}

Now suppose you want to do an innocent request like getting a list of all the countries on the planet

By default, ActiveRecord/Nhibernate will load the entire tree, until the very last dependency.

It can turn out to be A LOT of SQL calls.

Okay, we can solve that with lazy loading [ActiveRecord(lazy=true)] but then whenever you want to do something neat like below

String text = "This country of " + country.CountryName + " has the following states:";

foreach(State s in country.States)
{
    text += s.StateName + " ";
}

With the lazy load activerecord attribute every time it fetches for s.StateName it's another sql call.

That's way too many sql calls.

Some friends of mine suggested using ActiveRecordBase methods such as FindAll(criteria).

But it ends up being really ugly and hard to read with all those Expression.Eq and what not.

And then other people also suggested using something like HQL. HQL looks A LOT like SQL.

So bottom line, it seems like the most clean and optimized way is to write plain and simple SQL queries. It goes straight to the point.

SELECT * FROM Countries //No loading of an entire tree of dependencies

SELECT * FROM States WHERE CountryId = @selectedId   //1 call and you have all your states

Anyways, for now I'm using SQL queries and stored procedures for fetching data and ActiveRecord for saving/deleting objects.

Please correct me if I'm wrong... ?

Thanks Appreciated.

Answer 1

The recommended way is using lazy mappings by default, then eagerly fetch what you need for each case.

If you don't use lazy by default, you fetch pretty much the whole database in each query, as you already noticed.

If you use lazy but don't fetch eagerly, you run into the SELECT N+1 issue you noticed in your foreach example.

Everything I mentioned so far is independent of the query API you use. You can do all of this using either HQL, Criteria, Linq or QueryOver. Some queries are easier to express in HQL, and some other times it's more convenient to use Criteria, Linq or QueryOver. But again, this is an orthogonal question.

Also, this isn't so different from runnning raw SQL queries with ADO.NET. In your example, if you wanted a country with its states you would have INNER JOINed to get all states beforehand instead of issuing individual SELECts for each state.