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