R – How to you create Clustered Indexes with Fluent NHibernate

automappingclustered-indexfluent-nhibernatenhibernate

I am using Fluent-NHibernate (with automapping) to generate my tables but would like to choose a different clustered index than the ID field which is used by default. How can you create clustered indexes with Fluent NHibernate on a field other than the default Primary Key field?

The primary reasoning behind this is simple. I am using Guids for my primary key fields. By default, NHibernate creates clustered indexes on the primary key fields. Since Guids are usually not sequential, clustering on the primary key field causes a performance issue.

As we all know, appending records at the end of a table is a much cheaper operation than inserting records within the table. Also, the records in the table are physically stored in the order of the items in the clustered index. Since Guids are somewhat "random" and are not sequential, new Guids may be generated that are less than the value of other Id Guids already in the table–resulting in table inserts rather than appends.

To minimize this, I have a column called CreatedOn which is of type DateTime. I need for the table to be clustered on this CreatedOn column so that all new records are appended rather than inserted.

Any ideas for how to accomplish this are welcome!!!

Note: I realize that I could use Sequential Guids but prefer not to go down that path for security reasons.

Note: I still do not have an answer for this post but I have a few ideas I am pondering at the moment.

Using NHibernate without Fluent, I think it may be possible to created clustered indexes directly in NHibernate. I don't yet know enough about NHibernate to know how to do this. I am just pretty (as in almost absolutely) sure it can be done.
Fluent-NHibernate used to include a way to set attributes (e.g. like a clustered index) on a SQL object before the recent rewrite. Now that option appears to have gone away. I will probably post a question somewhere to see if that option is still available. If so, I could probably use that to set the clustered index.
Fluent-NHibernate provides the ability to expose a configuration for manual editing once it has been fluently built. I have not tried this functionality but expect it may offer the level of granularity that is needed to set clustered indexes.
Worst case scenario, I can write a SQL script to change the clustered indexes on all my tables once they are generated. However, I have a couple of questions regarding this approach. A. Since I am using automatic schema generation, will NHibernate "undo" my clustered index changes the next time it evaluates the configuration? 2. Will NHibernate error if it detects the clustered index has been changed? I need to test this but have not done so just yet. I really hate this solution though. I am testing my DB against SQLServer2008 and MySQL. Part of the beauty of NHibernate is that it is database agnostic. Once we introduce scripts, all bets are off.
There is an interface that is used in fluent conventions called IPropertyInstance Classes which inherit from this interface have an Index property which allows an Index to be created on the field. The problem is that there is no flag or other option to allow the index to be created as clustered. The simplest solution would be to add a property to this method to allow for clustered indexes to be created. I think I may suggest this to the Fluent-NHibernate developers.

Best Answer

This is an old post, but I hope could help someone else. This come from my experience on MS SQL Server. I believe different platforms require different solutions, but this should be a good starting point.

NHibernate doesn't set the CLUSTERED index on the primary key. It's the SQL Server default behavior. As there can be only one CLUSTERED per table, we need first to avoid the CLUSTERED creation on the primary key.

The only way I found to accomplish this is to create a custom Dialect, overriding the propery PrimaryKeyString. NHibernate's default comes from Dialect.cs:

    public virtual string PrimaryKeyString
    {
        get { return "primary key"; }
    }

For SQL Server

    public override string PrimaryKeyString
    {
        get { return "primary key nonclustered"; }
    }

This will force SQL Server to create a NONCLUSTERED primary key. Now you can add your own CLUSTERED index on your favorite column through the tag in the XML mapping file.

<database-object>
  <create>
    create clustered index IX_CustomClusteredIndexName on TableName (ColumnName ASC)
  </create>
  <drop>
    drop index IX_CustomClusteredIndexName ON TableName
  </drop>
</database-object>

Related Solutions

Sql-server – Should I get rid of clustered indexes on Guid columns

A big reason for a clustered index is when you often want to retrieve rows for a range of values for a given column. Because the data is physically arranged in that order, the rows can be extracted very efficiently.

Something like a GUID, while excellent for a primary key, could be positively detrimental to performance, as there will be additional cost for inserts and no perceptible benefit on selects.

So yes, don't cluster an index on GUID.

As to why it's not offered as a recommendation, I'd suggest the tuner is aware of this fact.

Fluent NHibernate: How to create one-to-many bidirectional mapping

To get a bidirectional association with a not-null foreign key column in the Details table you can add the suggested Owner property, a References(...).CanNotBeNull() mapping in the DetailsMap class, and make the Summary end inverse.

To avoid having two different foreign key columns for the two association directions, you can either specify the column names manually or name the properties in a way that gives the same column name for both directions. In this case you I suggest renaming the Details.Owner property to Details.Summary.

I made the Summary id generated by increment to avoid problems when inserting into the table since Summary currenty has no columns besides id.

Domain:

public class Detail
{
    public int id { get; protected set; }
    public string ItemName { get; set; }

    // Renamed to use same column name as specified in the mapping of Summary.Details
    public Summary Summary {get; set;} 
}

public class Summary
{
    public Summary()
    {
        Details = new List<Detail>();
    }

    public int id { get; protected set; }
    public IList<Detail> Details { get; protected set; }
}

Mapping:

public class DetailMap : ClassMap<Detail>
{
    public DetailMap()
    {
        Id(x => x.id)
            .GeneratedBy.Native();

        Map(x => x.ItemName)
            .CanNotBeNull();

        References<Summary>(x => x.Summary)
            // If you don't want to rename the property in Summary,
            // you can do this instead:
            // .TheColumnNameIs("Summary_id")
            .CanNotBeNull();
    }
}

public class SummaryMap : ClassMap<Summary>
{
    public SummaryMap()
    {
        Id(x => x.id)
            .GeneratedBy.Increment();

        HasMany<Detail>(x => x.Details)
            .IsInverse()
            .AsBag(); // Use bag instead of list to avoid index updating issues
    }
}

Best Answer

Related Solutions

Sql-server – Should I get rid of clustered indexes on Guid columns

Fluent NHibernate: How to create one-to-many bidirectional mapping

Related Topic