Max # of records in n2Details table

Topics: Developer Forum
May 25, 2008 at 9:50 PM
Hello,

(cannot stop raving how wonderful piece of work N2 is, thank you Cristian ! :-)

Recently I've put N2 under stress, loading it with my product catalog (with ca. 24000 items), each of which having some dozens of "properties" (Details). What i've got is the following exception on the top of the stack after importing ~2100 records into [n2Item] table and ~59000 into [n2Details] table:

Unhandled Exception: NHibernate.Exceptions.GenericADOException: could not insert: [N2.Details.StringDetail][SQL: INSERT INTO n2Detail (ItemID, DetailCollectionID, Name, StringValue, Type) VALUES (?, ?, ?, ?, 'String'); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: String or binary data would be truncated.

Loading is performed via N2.Tests.PersistenceAwareBase functionality into Sql Server 2005 Express database.

Looking at the exception and the number of records in [n2details] when import broke, i well suspect the problem might be with N2 not supporting record number over int32 index boundary. A brief glance at N2.Details.DetailCollection suggest this hypothesis too.

What are true limits on a number of records in N2 tables, supported by N2's API ?

Thank you.

/Taras
May 25, 2008 at 10:47 PM
Sorry for the false alarm, indeed my problem was caused by Detail's name exceeding 50 characters (as it is carved into N2's NHibernate mapping).

But still i'm curious to what extent N2 storage capabilities could be pushed ?
Coordinator
May 25, 2008 at 11:11 PM
Glad you like it =) I havn't really digged into this. I've only done tests with ten thousands items or so and I thought it would be plenty for most web sites.

The teoretical limit is around 2 billion items and details but I'm not convinced the model with itam-detail tables scales for large numbers of items. Did you make any benchmarking you can share?
May 27, 2008 at 12:27 PM
Edited May 27, 2008 at 12:29 PM
Too early for any sort of judgments, but here is what i've got with my import:

It takes normally roughly 1 hour to import ~24000 records into [N2Item] table with ~700 000 corresponding items going into [N2Detail]. These figures are not very descriptive per se, as my importing code runs in parallel with html scraping (from a local cache, via HtmlAgilityPack if you're curious) and includes some in-memory pre-processing. What's more descriptive, is that the time is quite close to that of storing the very same data and doing exactly the same processing into separate per-item xml files with XLinq (~ 45 min).

Much more interesting discovery was to find that the item hierarchy shape is decisive for performance of OLTP-like transactions. Trying to store a plain stream of items, calling Persister.Save() after each, the progress nearly stalls after 1500 th. To be exact, i'm storing not really a stream of unrelated item, but rather shaping a simple Category-with-Items hierarchy.

foreach( Category ) {
  var category = CreateOneItem<Category>(0, "Name", Root)

  ///Slow
  //   engine.Persister.Save(category);

  foreach ( Item ) {
    CreateOneItem<Item>(0, "Child", category);
  }
   
  ///Fast
  engine.Persister.Save(category);
}

Calling Persister.Save *after* populating Children collection is nearly 100 times faster than *before* it. A quick glance into code revealed that behavior is probably due to NHibernate transaction usage.

All in all, it's still early for any real measurements. If i find nothing interesting, i'll be silent like a fish, i promise ;-)