I am running a query on a table with mote than 100000 rows in it. When quering rows I use a where to limit the result to about 4000 rows but when I look in the log generated I can see that all rows are loaded anyway.

When running the generated SQL in another tool the query returns the correct number of rows. I have pasted some of the lines from the log below. Any Idea why?

Hibernate version: 1.0.1.0

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:
2006-01-17 09:24:23,222 [2268] DEBUG NHibernate.Hql.QueryTranslator [(null)] - HQL: from Domain.Kunde ku where ku.Navn like 'A%' order by ku.Navn
2006-01-17 09:24:23,222 [2268] DEBUG NHibernate.Hql.QueryTranslator [(null)] - SQL: select kunde0_.ID as ID, kunde0_.Modulnr as Modulnr, kunde0_.Telefaks as Telefaks, kunde0_.Nedlagtdato as Nedlagt16_, kunde0_.Bankkontonr as Bankkon14_, kunde0_.Juridisk as Juridisk, kunde0_.Land as Land, kunde0_.Navn as Navn, kunde0_.Oppstartsdato as Oppstar15_, kunde0_.Postnr as Postnr, kunde0_.Fylke as Fylke, kunde0_.Kommune as Kommune, kunde0_.Modul as Modul, kunde0_.Telefon as Telefon, kunde0_.Poststed as Poststed, kunde0_.Kundetypenr as Kundety11_ from ADM_Kunde kunde0_ where (kunde0_.Navn like 'A%') order by kunde0_.Navn
2006-01-17 09:24:23,232 [2268] DEBUG NHibernate.Impl.BatcherImpl [(null)] - Opened new IDbCommand, open IDbCommands :1
2006-01-17 09:24:23,232 [2268] DEBUG NHibernate.Impl.BatcherImpl [(null)] - Building an IDbCommand object for the SqlString: select kunde0_.ID as ID, kunde0_.Modulnr as Modulnr, kunde0_.Telefaks as Telefaks, kunde0_.Nedlagtdato as Nedlagt16_, kunde0_.Bankkontonr as Bankkon14_, kunde0_.Juridisk as Juridisk, kunde0_.Land as Land, kunde0_.Navn as Navn, kunde0_.Oppstartsdato as Oppstar15_, kunde0_.Postnr as Postnr, kunde0_.Fylke as Fylke, kunde0_.Kommune as Kommune, kunde0_.Modul as Modul, kunde0_.Telefon as Telefon, kunde0_.Poststed as Poststed, kunde0_.Kundetypenr as Kundety11_ from ADM_Kunde kunde0_ where (kunde0_.Navn like 'A%') order by kunde0_.Navn
2006-01-17 09:24:23,232 [2268] INFO NHibernate.Loader.Loader [(null)] - select kunde0_.ID as ID, kunde0_.Modulnr as Modulnr, kunde0_.Telefaks as Telefaks, kunde0_.Nedlagtdato as Nedlagt16_, kunde0_.Bankkontonr as Bankkon14_, kunde0_.Juridisk as Juridisk, kunde0_.Land as Land, kunde0_.Navn as Navn, kunde0_.Oppstartsdato as Oppstar15_, kunde0_.Postnr as Postnr, kunde0_.Fylke as Fylke, kunde0_.Kommune as Kommune, kunde0_.Modul as Modul, kunde0_.Telefon as Telefon, kunde0_.Poststed as Poststed, kunde0_.Kundetypenr as Kundety11_ from ADM_Kunde kunde0_ where (kunde0_.Navn like 'A%') order by kunde0_.Navn
2006-01-17 09:24:23,232 [2268] DEBUG NHibernate.SQL [(null)] - select kunde0_.ID as ID, kunde0_.Modulnr as Modulnr, kunde0_.Telefaks as Telefaks, kunde0_.Nedlagtdato as Nedlagt16_, kunde0_.Bankkontonr as Bankkon14_, kunde0_.Juridisk as Juridisk, kunde0_.Land as Land, kunde0_.Navn as Navn, kunde0_.Oppstartsdato as Oppstar15_, kunde0_.Postnr as Postnr, kunde0_.Fylke as Fylke, kunde0_.Kommune as Kommune, kunde0_.Modul as Modul, kunde0_.Telefon as Telefon, kunde0_.Poststed as Poststed, kunde0_.Kundetypenr as Kundety11_ from ADM_Kunde kunde0_ where (kunde0_.Navn like 'A%') order by kunde0_.Navn

Please post your code. The generated SQL looks correct given the HQL string.

The code is Object pascal (Delphi) but I think this should be quite readable :

procedure TWinForm.TestForum;
var
query : IQuery;
List : IList;
i, j : Integer;
vKunde : Kunde;
begin
txn := session.BeginTransaction;
try
query := session.CreateQuery( 'from Domain.Kunde ku where ku.Navn like ''A%'' order by ku.Navn');
List := query.List;
txn.Commit;
except
txn.Rollback;
raise;
end;

ListBox1.Items.Clear;
for i := 0 to List.Count - 1 do
begin
vKunde := List[i] as Kunde;
ListBox1.Items.Add(vKunde.Navn);

if vKunde.Adresser.Count > 0 then
ListBox1.Items.Add(' - '+(vKunde.Adresser[I] as Kundeadresse).Adresselinje1);
end;
end;

The query.List.Count is right (about 4000) but looking in the log file generated by Log4Net I can see that all 110000 records are processed/loaded and I guess that that can't be right.

Just curious, what in the log makes you feel that it fetched all 110000 records and not 4000

All the objects that was loaded was shown in the log and I found pleanty of rows from table where "Navn like 'A%'" was not true and as I ordered by Navn (Name) I saw that the last one loaded was the last in the alfabet. After that I concluded that it must have been loading all rows. Besides I could see from the memory consumption (which was what first made me suspicious, + load time) that this seemed to be the same wether I queried all rows or just "Navn like 'A%'"

Looks like your 4000 objects have references to other objects and the classes are not made lazy, so NH has to load all of them.

Set as many classes as possible to lazy="true" (and don't forget to declare all their methods and properties as virtual) and the problem should go away.