How do you optimize Castle ActiveRecord calls

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 m开发者_Go百科any 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.

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.

This is a well-known issue with ORMs. One way to solve this is to add the BatchSize nhibernate setting so that each trip to the db you bring back n records (States in this case) instead of one.

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

That sounds really weird, sorry to say!

One big advantage of ORM's is that you can abstract your database away...! So, yes, HQL may feel, look and sound like SQL, but it has one big difference: it is DBMS independent. The same HQL will work for SQL Server, or Oracle, or MySQL...

You will also need some work for sharing your database connection between NHibernate and SQL Server. Feasible, but a little bit ugly.

I would go with HQL without even thinking about it. Sometimes you really need to get to a lower level and write some SQL queries, but the scenario you're describing is far from being in that case.

OTOH, on real world scenarios you'll probably need pagination for displaying so much data. And if that's the case, the lazy approach will be much better, even if it potentially generates much more SQL statements.

Just think about it: if a user queries a result set of 1,000,000 records, but he/she is seeing just the first 20 entries on a web page, I don't need to fetch ALL 1,000,000 records from the database. And that's exactly what lazy loading is meant for...

Edit: almost forgot NHibernate first and second level caching. Using this your application may avoid querying the DBMS layer. That alone may translate in a HUGE performance boost...