Hello!
I am using Orchard CMS <http://orchardproject.net/> which is under the
covers using NHibernate (specifically Version=3.3.1.4000). The problem that
we have is that for some reasonably sized tables (1,700 rows) we see
absolutely awful performance from NHibernate, however a direct query is
extremely fast. I have run the NHibernateProfiler and it shows that the
result contains the expected number of results, but for duration it says
(as an example) 68 ms/6687ms. When I run the query from
SqlServerManagerment studio I get latency of approximately matching the
68ms, nothing on the order of 6 sesconds.
The types in question here is defined as following:
public class ContentTypeDefinitionRecord {
public ContentTypeDefinitionRecord() {
ContentTypePartDefinitionRecords = new List<
ContentTypePartDefinitionRecord>();
}
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual string DisplayName { get; set; }
public virtual bool Hidden { get; set; }
[StringLengthMax]
public virtual string Settings { get; set; }
[CascadeAllDeleteOrphan]
public virtual IList<ContentTypePartDefinitionRecord>
ContentTypePartDefinitionRecords { get; set; }
}
public class ContentTypePartDefinitionRecord {
public virtual int Id { get; set; }
public virtual ContentPartDefinitionRecord
ContentPartDefinitionRecord { get; set; }
[StringLengthMax]
public virtual string Settings { get; set; }
}
public class ContentPartDefinitionRecord {
public ContentPartDefinitionRecord() {
ContentPartFieldDefinitionRecords = new List<
ContentPartFieldDefinitionRecord>();
}
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual bool Hidden { get; set; }
[StringLengthMax]
public virtual string Settings { get; set; }
[CascadeAllDeleteOrphan]
public virtual IList<ContentPartFieldDefinitionRecord>
ContentPartFieldDefinitionRecords { get; set; }
}
The query itself is:
var result = _typeDefinitionRepository.Table
.FetchMany(x => x.ContentTypePartDefinitionRecords)
.ThenFetch(x => x.ContentPartDefinitionRecord)
.ToList();
This is the NHibernateConfiguration:
.SetProperty(NHibernate.Cfg.Environment.FormatSql, Boolean.FalseString)
.SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, Boolean.
FalseString)
.SetProperty(NHibernate.Cfg.Environment.Hbm2ddlKeyWords, Hbm2DDLKeyWords.
None.ToString())
.SetProperty(NHibernate.Cfg.Environment.PropertyBytecodeProvider, "lcg")
.SetProperty(NHibernate.Cfg.Environment.PropertyUseReflectionOptimizer,
Boolean.TrueString)
.SetProperty(NHibernate.Cfg.Environment.QueryStartupChecking, Boolean.
FalseString)
.SetProperty(NHibernate.Cfg.Environment.ShowSql, Boolean.FalseString)
.SetProperty(NHibernate.Cfg.Environment.StatementFetchSize, "100")
.SetProperty(NHibernate.Cfg.Environment.UseProxyValidator, Boolean.
FalseString)
.SetProperty(NHibernate.Cfg.Environment.UseSqlComments, Boolean.FalseString)
.SetProperty(NHibernate.Cfg.Environment.WrapResultSets, Boolean.TrueString)
.SetProperty(NHibernate.Cfg.Environment.PrepareSql, Boolean.TrueString)
.SetProperty(NHibernate.Cfg.Environment.BatchSize, "256")
I collected a Dottrace and it looks like the time is all spent in the
System.Data.SqlClient.SqlDataReader.IsDBNull(Int32) function, although I
suspect the issue is that we are actually lazy loading.
http://i.imgur.com/mEUTq8t.png
And here is the NHibernate Profiler log for the call:
http://imgur.com/y5AEpO4
Anyone have any ideas?
Thanks,
Darrenh
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
