Urgent issue : how can I see generated queries by nhibernate?

Topics: Customizing Orchard, Troubleshooting, Writing modules
Jun 27, 2012 at 4:07 PM
Edited Jun 27, 2012 at 4:08 PM

We ran into a critical issue where for some reason a SELECT * FROM A where A.B_ID = ? is used without a where clause.

This causes an extreme load on our web/sql server and we're 5 days from going live, but we only have <2 days left for it to be fixed :/

Anyone know how I can 'see' the queries that nhibernate generates so I can know when I actually solved the issue (as in the 'evil' query isn't executed anymore then)

Jun 27, 2012 at 4:37 PM

As I happened to need this once:

Set the following in \src\Orchard.Web\Config\log4net.config:


   <priority value="ALL" /

   <appender-ref ref="debugger"/


This way queries will be saved to the log: \src\Orchard.Web\App_Data\Logs\orchard-debug*.log Search for „NHibernate.SQL” or "NHibernate.SQL.TableName".

Jun 27, 2012 at 5:11 PM

Ok I found something much easier to use.

NHibernate Profiler (@ http://nhprof.com)

I just included its appender dll, called the init command @ 'boot' and now I can see all queries in detail.

Also, I found my problem:

record.Pulses.Where(p => p.TimePassed <= maxTimePassed).OrderByDescending(p => p.Id).FirstOrDefault()

This generates a query that fetches ALL pulses for the player (player being the 'record' here) and doesn't do any 'where' filtering @ the sql side.

I replaced this line with:

_dataService.Pulses.Table.Where(p => p.TrackerServerPlayerRecord.Id == record.Id && p.TimePassed <= tp).OrderByDescending(p => p.Id).FirstOrDefault()

And now it DOES do the filtering sql server-side.

Any specific reason why the where on the 'Pulses' (an IList, properly mapped afaik) isn't handled sql server-side,, and the query based on all pulses is?

Jun 27, 2012 at 5:21 PM

What type is _dataService.Pulses of ?

Jun 27, 2012 at 5:22 PM
Edited Jun 27, 2012 at 5:25 PM

It is an IRepository.

@ the 'player' record I have:

[CascadeAllDeleteOrphan] public virtual IList<TrackerServerPlayerPulseRecord> Pulses { get; private set; }

edit: I think Pulses.Add() shows the same problem (testing further though)

Jun 27, 2012 at 5:25 PM

By the way, if you go on prod, you might want to enable the Cache module. Even without configuring anything in there.

Jun 27, 2012 at 5:27 PM

Err, it is for a live race server ranking screen, don't think cache will be of any use ;)

If we get it on the air, I'll give out more details on exactly what we did, but I got one more day to get things working smoothly, or the TV guys will reject it :/

Jun 27, 2012 at 5:29 PM
Edited Jun 27, 2012 at 5:39 PM

Ok,so yes, this causes the 'select all' as well!


ToRecord is simply 'create a new record with values from some other non-record object' so that shouldn't be it. I can add things like this right?

edit: So yeah, this seems to be my final issue at the moment. If I do the 'Add' it does the fetch all :/ 

edit2: So I changed my code to this:

var pulseRecord = pulse.ToRecord();
pulseRecord.TrackerServerPlayerRecord = player;

And the issue seems to be resolved fully now. None of the queries fetch 100+ rows anymore. Any idea what the issue could be with the list?