How to have NHibernate use a SQL MERGE INTO statement

On our project we were noticing a massive performance penalty when sending a number of records to the database to either be inserted or updated via the standard NHibernate framework syntax, so we decided to see if we could do anything about this. Turns out that we were able to alter the query that NHibernate normally generated, and change it into a MERGE INTO type statement, which meant a huge performance gain, as SQL was now doing the de-duping process itself on the server, which was always going to be more efficient.

The purpose of this blog is to outline the high level steps that we needed to implement in order to allow the MERGE INTO statement to execute.

The changes were really concentrated into a SQL statement constructor utility class as follows…

/// <summary>
/// This class holds definitions for common SQL statements to be executed
/// against the DB directly
/// </summary>
public static class SqlStatementConstructor
{
   /// <summary>
   /// Creates the TSQL statement to adds the items to custom list via a MERGE operation.
   /// </summary>
   /// <param name="customListId">The custom list id.</param>
   /// <param name="selectionQuery">The selection query.</param>
   /// <param name="customListType">Type of the custom list.</param>
   /// <returns>
   /// A TSQL statement that can be executed against the DB.
   /// </returns>
   public static string AddItemsToCustomList(int customListId, DetachedCriteria selectionQuery, ListType customListType)
   {
       // First, add the ID projection to the selection query, there's no need to pull any other fields.
       selectionQuery.SetProjection(Projections.Id());

       // Prepare the selectionQuery and extract the NH name of the ID column.
       // The format of the initial selected field (which is the one we're after) will be:
       // SELECT this_.ID as XXXX FROM... its very likely that NH will always use the variable y0_ as the ID
       // but we always extract rather than assume.

       string itemSelectionQuery = selectionQuery.ToSql();

       int endOfFirstAs = itemSelectionQuery.IndexOf(" as ") + 4;
       int lengthOfIdFieldName = itemSelectionQuery.IndexOf(" FROM ") - endOfFirstAs;
       string selectionQueryIdName = itemSelectionQuery.Substring(endOfFirstAs, lengthOfIdFieldName);
       string customListField = GetCustomListFieldName(customListType);

       // Now add our custom list id into the select in the correct place very simply by inserting after the initial SELECT.
       itemSelectionQuery = itemSelectionQuery.Insert(6, string.Format(" {0} as ListID, ", customListId));

       StringBuilder sb = new StringBuilder();

       sb.Append("MERGE dbo.CustomListValue AS Target");
       sb.Append(" USING (");
       sb.Append(itemSelectionQuery);
       sb.Append(") AS Source");
       sb.Append(string.Format(" ON (Target.CustomListID = Source.ListID AND Target.{0} = Source.{1})", customListField, selectionQueryIdName));
       sb.Append(" WHEN MATCHED THEN");

Read more: EMC Consulting

Posted via email from .NET Info