Try fast search NHibernate

05 April 2012

NHibernate: autocreate indexes for foreignkey

In these last days we have fallen in a performance issue with one of our DBs; the last and littlest one.

The creation of indexes on FKs seems to be a best-practice for MS-SQL-server and ORACLE and is not needed with Firebird; in Firebird instead than a best-practice is the default behavior: the FK includes an index.

That said I have to be sure that each FK has to have an index. After check our mappings I have realized that no one of our tables have the corresponding index for each FK.
Big PITA ?… no! that has been the lucky, because now I can create a very little piece of code to let NHibernate create all needed indexes for me.
public static class NHibernateConfigurationExtensions
{
    private static readonly PropertyInfo TableMappingsProperty =
        typeof(Configuration).GetProperty("TableMappings", BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);

    public static void CreateIndexesForForeignKeys(this Configuration configuration)
    {
        configuration.BuildMappings();
        var tables = (ICollection<Table>)TableMappingsProperty.GetValue(configuration, null);
        foreach (var table in tables)
        {
            foreach (var foreignKey in table.ForeignKeyIterator)
            {
                var idx = new Index();
                idx.AddColumns(foreignKey.ColumnIterator);
                idx.Name = "IDX" + foreignKey.Name.Substring(2);
                idx.Table = table;
                table.AddIndex(idx);
            }
        }
    }
}
and this is the code to have the migration-step script.
var configuration = CreateConfiguration();
configuration.CreateIndexesForForeignKeys();
var sb = new StringBuilder(500);
new SchemaUpdate(configuration).Execute(s => sb.Append(s), false);
Console.WriteLine(sb.ToString());
That is all… few minutes and everything done and checked for now on.

P.S. perhaps I'll be back to my blog...perhaps

15 comments:

  1. Welcome back to the land of the blog :)

    ReplyDelete
  2. Nice to know you're alive... and kicking... and haven't turned to Entity Framework! ;-)

    ReplyDelete
  3. Yeah! I was missing your 'words of wisdom' on the NHibernate world. This is a very nice tip by the way, thanks for sharing.

    ReplyDelete
  4. Welcome back =) Nice post and thanks!

    ReplyDelete
  5. Welcome back!!!!

    ...and always great!

    :))

    ReplyDelete
  6. Nice trick but, know what? with entity framework and fluent nhibernate this is even easier :-P

    Glad to see you back!

    ReplyDelete
  7. @Juanma
    I don't think so. Let me see the code.

    ReplyDelete
  8. Fabio,

    I'd like to know how can I extend some LINQ class, so I can add a with(nolock) hint to every query performed using LINQ !

    Thanks !

    ReplyDelete
  9. @leonardo:
    You can write your own interceptor, inheriting from EmptyInterceptor, and overriding the OnPrepareStatement method. You set it on the Configuration object and build your ISessionFactory after it:

    using (ISessionFactory factory = cfg.SetInterceptor(new MyInterceptor()).BuildSessionFactory()) { ... }

    RP

    ReplyDelete
  10. Awesome tip, just saved me allot of dull, error prone work. Added it to our product and bang, it's allot faster.

    ReplyDelete
  11. Using NHibernate few years ago and could not find the source of this post, if not indicate much work as I can:
    http://nhforge.org/blogs/nhibernate/archive/2008/11/16/mapping-source-how-map-a-class-without-use-nothing.aspx

    ReplyDelete
  12. Heber
    This is WAY easier to use than NHibernate:
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

    ReplyDelete
  13. too old a post, but relying on a non-public property seems a bit risky...

    ReplyDelete