Databases
StackOverflow2013
Postsdbo
PONHibernate insert/lookup performance
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate insert/lookup performance
    text
    copied!<p>I have several XML files and each file contains data of ‘root objects’ which I parse using Linq to XML and then create actual root objects which I persist using NHibernate and the sharp architecture repository. I have started to optimise the data insert and manage to add 30000 objects in about 1 hour and 40 minutes to the database. However, this is still too slow.</p> <p>I think one bottle neck is the lookup of objects in the database which requires IO. Objects have to be looked up for reuse.</p> <p>The root object has several authors:</p> <pre><code>public virtual IList&lt;Author&gt; Authors { get; set; } </code></pre> <p>Authors have this structure:</p> <pre><code>public class Author : Entity { public virtual Initials Initials { get; set; } public virtual ForeName ForeName { get; set; } public virtual LastName LastName { get; set; } } </code></pre> <p>I have achieved a great speed up by using a typed Id (something I wouldn't normally do):</p> <pre><code>public class LastName : EntityWithTypedId&lt;string&gt;, IHasAssignedId&lt;string&gt; { public LastName() { } public LastName(string Id) { SetAssignedIdTo(Id); } public virtual void SetAssignedIdTo(string assignedId) { Id = assignedId; } } </code></pre> <p>Which I look up (and potentially create) like this:</p> <pre><code>LastName LastName = LastNameRepository.Get(TLastName); if (LastName == null) { LastName = LastNameRepository.Save(new LastName(TLastName)); LastNameRepository.DbContext.CommitChanges(); } Author.LastName = LastName; </code></pre> <p>I am looking authors up like this:</p> <pre><code>propertyValues = new Dictionary&lt;string, object&gt;(); propertyValues.Add("Initials", Author.Initials); propertyValues.Add("ForeName", Author.ForeName); propertyValues.Add("LastName", Author.LastName); Author TAuthor = AuthorRepository.FindOne(propertyValues); if (TAuthor == null) { AuthorRepository.SaveOrUpdate(Author); AuthorRepository.DbContext.CommitChanges(); Root.Authors.Add(Author); } else { Root.Authors.Add(TAuthor); } </code></pre> <p>Can I improve this? Should I use stored procedures/HQL/pure SQL/ICriteria instead to perform the lookup? Could I use some form of caching to speed up the lookup and reduce IO? The CommitChanges seems to be necessary or should I wrap everything into a transaction?</p> <p>I already flush my session etc. every 10 root objects.</p> <p>Any feedback would be very much welcome. Many thanks in advance.</p> <p>Best wishes,</p> <p>Christian</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload