A Note about NHibernate Generators and Clustered Indexes

My previous article emphasized that you shouldn’t mix clustered indexes and identity fields.  However, if you’re using NHibernate you already probably know you shouldn’t be using identity fields.  On the other hand, the points still generalize to some of the NHibernate generators:

  • Increment: just as bad as identity
  • HiLo:  better than identity, but not by much.  Don’t mix with a clustered index.
  • GUID:  Extremely random.  In fact, probably too random.  The inserts get plastered everywhere and can hurt performance.
  • GUID Comb: Better than GUID,

In short: still don’t put a clustered index on an identity field.  Modifying GUID comb to include the thread ID might actually make it viable.

Again, we’re talking OLTP here.  If you read the original article, Jimmy Nilsson measures batch insert performance, and the concurrency implications never come up.

Published by

Julian Birch

Full time dad, does a bit of coding on the side.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s