How to Optimize Castle ActiveRecord Calls

Question

How to optimize ActiveRecord calls in ASP.NET MVC 2 web applications?

I am sitting in front of my project, and everything is fine until I start filling in the data. Like many projects, I have a data structure similar to this:

There are many countries in the planet. There are many states / provinces in the country. There are many cities in the state. The city has many areas.

The following is an example of an Active Directory / Active Directory / NHibernate persistent 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 make an innocent request, for example, get a list of all the countries on the planet

By default, ActiveRecord / Nhibernate will load the entire tree to the most recent dependency.

It may turn out to be LOTS of SQL calls.

, [ActiveRecord(lazy=true)], , - ,

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

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

lazy load activerecord , s.StateName sql.

sql.

ActiveRecordBase, FindAll ().

, Expression.Eq, .

- HQL. HQL LOT, SQL.

, , - SQL-. .

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

, SQL- ActiveRecord / .

, , ...?

.

Answer 1

- , , .

, , .

, , SELECT N + 1, foreach.

, , API . , HQL, Criteria, Linq, QueryOver. HQL, , Linq QueryOver. , .

, SQL- ADO.NET. , , INNER JOINed , SELECts .

Answer 2

ORM. - BatchSize nhibernate, db n ( ) .

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

Answer 3

, !

ORM , ...! , , HQL , SQL, : . HQL SQL Server, Oracle, MySQL...

NHibernate SQL Server. , .

HQL, . SQL-, , , .

OTOH, , , . , SQL-.

: 1 000 000 , 20 -, 1 000 000 . , ...

Edit: I almost forgot NHiernate's first and second level caching. Using this, your application may not request a DBMS level. This in itself can lead to an increase in HIGHGE ...