nHibernate with hiLo id generation- 'select N+1' when saving an object graph

Question

for the example's sake, let's say my application's domain is a health club, where customers purchase gym memberships. these memberships include a collection of priviliges, and also a record of the customer's fitness.

public class Customer
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        //... some more properties
        public Membership Membership { get; set; }
    }

class Membership
{
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public FitnessRecord FitnessRecord { get; set; }
    public IEnumerable<Privilige> MemberPriviliges { get; set; }

}

class FitnessRecord
{
    public DateTime Date { get; set; }
    public int NumberOfPushups { get; set; }
    //etc...
}

class Privilige
{
    public ResourceType Resource { get; set; } //could be 'swimming pool', 'sauna' etc..
    public PriviligeType Type { get; set; } //day pass / month pass etc..
}

when a new customer is created, all of those objects are instansiated and saved. I'm using hiLo for Id generation. this results in a 'update N+1' problem, like so:

2011-04-21 17:48:52:468 DEBUG MyApp.Managers - saving Membership with ID 0. 
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Reading high value:select next_hi from dbo._uniqueKey with (updlock, rowlock)
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Updating high value:update dbo._uniqueKey set next_hi = @p0 where next_hi = @p1;@p0 = 28735, @p1 = 28734
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Reading high value:select next_hi from dbo._uniqueKey with (updlock, rowlock)
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Updating high value:update dbo._uniqueKey set next_hi = @p0 where next_hi = @p1;@p0 = 28736, @p1 = 28735
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Reading high value:select next_hi from dbo._uniqueKey with (updlock, rowlock)
2011-04-21 17:48:52:484 DEBUG NHibernate.SQL - Updating high value:update dbo._uniqueKey set next_hi = @p0 where next_hi = @p1;@p0 = 28737, @p1 = 28736
2011-04-21 17:48:52:500 DEBUG NHibernate.SQL - Reading high value:select next_hi from dbo._uniqueKey with (updlock, rowlock)
2011-04-21 17:48:52:500 DEBUG NHibernate.SQL - Updating high value:update dbo._uniqueKey set next_hi = @p0 where next_hi = @p1;@p0 = 28738, @p1 = 28737
2011-04-21 17:48:52:500 DEBUG MyApp.Managers - commiting transaction
2011-04-21 17:48:52:546 DEBUG NHibernate.SQL - Batch commands:
command 0:INSERT INTO dbo.[FitnessRecord] (..., ..., ..., ..., ..., ..., Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);@p0 = 21/04/2011 17:48:52, @p1 = 25, @p2 = NULL, @p3 = False, @p4 = NULL, @p5 = 5, @p6 = 28736

2011-04-21 17:48:52:546 DEBUG NHibernate.SQL - Batch commands:
command 0:INSERT INTO dbo.[Membership] (..., ..., ..., ..., ..., FitnessRecord_id,..., Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);@p0 = 'Stew', @p1 = 'SIMULATED', @p2 = False, @p3 = '300', @p4 = False, @p5 = 28736, @p6 = 28726, @p7 = 28735

2011-04-21 17:48:52:546 DEBUG NHibernate.SQL - Batch commands:
command 0:INSERT INTO dbo.[Privilige] (...,..., ..., ..., ..., ..., ..., ..., ..., Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);@p0 = 'BRS', @p1 = 'DST', @p2 = 'SN', @p3 = 21/04/2011 00:00:00, @p4 = '2056', @p5 = '010A', @p6 = 'M', @p7 = 'A35', @p8 = False, @p9 = 28737
command 1:INSERT INTO dbo.[Privilige] (...,..., ..., ..., ..., ..., ..., ..., ..., Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);@p0 = 'BRS', @p1 = 'DST', @p2 = 'SN', @p3 = 21/04/2011 00:00:00, @p4 = '02056', @p5 = '010A', @p6 = 'M', @p7 = 'A31', @p8 = False, @p9 = 28742

2011-04-21 17:48:52:546 DEBUG NHibernate.SQL - Batch commands:
command 0:INSERT INTO dbo.[Customer] (..., ..., ..., ..., Membership_id, ..., Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);@p0 = 21/04/2011 17:48:52, @p1 = 'ValidEntryScan', @p2 = 'ValidEntryScan', @p3 = False, @p4 = 28735, @p5 = 16, @p6 = 28734

2011-04-21 17:48:52:546 DEBUG NHibernate.SQL - Batch commands:
command 0:UPDATE dbo.[FitnessRecord] SET ... = @p0, ...= @p1, ...= @p2, ...= @p3, Membership_id = @p4, ...= @p5 WHERE Id = @p6;@p0 = 21/04/2011 17:48:52, @p1 = 25, @p2 = NULL, @p3 = False, @p4 = 28735, @p5 = 5, @p6 = 28736

2011-04-21 17:48:52:562 DEBUG NHibernate.SQL - Batch commands:
command 0:UPDATE dbo.[Privilige] SET Membership_id = @p0 WHERE Id = @p1;@p0 = 28735, @p1 = 28737
command 0:UPDATE dbo.[Privilige] SET Membership_id = @p0 WHERE Id = @p1;@p0 = 28735, @p1 = 28742

2011-04-21 17:48:52:562 DEBUG MyApp.Managers - finished

notice the fact that even though i'm using batching, it still takes ~100 ms for those records to be saved; that seems a little excessive, isn't it? is there any way to improve on that?

100 ms doesn't seem that bad to me for 5 inserts and 3 updates. — Cole W, Apr 21 '11 at 15:39
isn't the first **78ms** reading/updating the high values which would only happen say once in a **hundred** saves. — Rippo, Apr 21 '11 at 15:41

score 0 · Accepted Answer · answered Apr 21 '11 at 18:14

0

There's no such thing as "update N+1" problem.

Since HiLo is retrieved/updated ONCE every max_lo items, those statements would only occur in your first execution, with the times being effectively ZERO for the following calls.

answered Apr 21 '11 at 18:14

Diego Mijelshon

52,548
16
116
154

I see these statements on EACH execution. can you elaborate on 'HiLo is retrieved/updated ONCE every max_lo items'? perhaps somewhere I have a wrong setting? – J. Ed Apr 24 '11 at 13:09
@sJhonny: http://stackoverflow.com/questions/282099/whats-the-hi-lo-algorithm If you see that all the time it's either because you're restarting the application, or you're setting max_lo to 1; that's not a realistic test of the hilo generator. – Diego Mijelshon Apr 24 '11 at 14:36
absolutely right. I set the max_lo to 0, which is a stupid mistake on my part. – J. Ed Apr 26 '11 at 06:57

nHibernate with hiLo id generation- 'select N+1' when saving an object graph

1 Answers1