воскресенье, 8 ноября 2009 г.

LINQ Bulk Insert

Недавно я столкнулся с проблемой произовдительности вставки данных с помощью L2S. Представьте себе следующую ситуацию: у нас e-commerce сайт, в котором можно создавать продукты, категории и мапить продукты на категорию (ии). Остановимся на функционале, где можно мапить продукты на категорию. У вас есть форма для редактирования категории, на которой в таблице отображаются продукты. Таблица пейжирована. Также есть 2 чекбокса - check\ unckeck all и check\unckeck current page ну и кнопка Save.




Вы отмечаете check all нажимаете Save и жопа ждете пока сохраниться мап всех товаров, которые у вас есть на категорию. Не важно большой у вас ассортимент или нет, это будет выполнятся долго, т.к. будет дергаться INSERT INTO CategoryToProductMap столько раз, сколько у вас товаров. В моем случаей это было 10 000 раз. Потом я встретил еще чудо-код, который выполнял подобные действия 130 000 раз ! Вы представляете это !? А если хотя бы 2 человека попадут на эту страницу...

В данной статье мы поговорим о том как можно оптимзировать процесс вставки большого количества данных, большого значит > 1 000. Хотя вы сможете применять это по своим нуждам.

В MS SQL 2000 появилась команда BULK INSERT, которая возволяет загрузить данные из файла в SQL Server "пачкой". Внутри эта команда использует утилиту BCP, копирует данные из файла в указанную таблицу. В ADO.NET 2.0 появился класс SqlBulkCopy, который является обверткой над командой BULK INSERT. В его использовании нет ничего сложного. Вы указываете строку соединения, таблицу в которую хотите вставить данные и сами данные.
Это нам подходит! Надо бы сделать это гламурненько в стиле L2S.
Погуглив, я нашел пример как это делается в стиле L2S, но как по мне он сложноват как в исполнении так и в чтении. Если хотите, можете взять его, он рабочий, я проверил. Но! У меня получилось быстрей :). Почему - не знаю. В конце статьи я приведу примеры по скорости выполнения.
Для реализации своего примера я почерпнул пару штрихов из источника, поэтому желательно если вы с ним ознакомитесь, хотя и необязательно. Примеры я буду приводить на всем известной Northwind базе данных. В результате у нас получится слудующее:

IEnumerable<Customer> dataToInsert = GenerateCustomers(100000);
int batchSize = 5000;
context.Customers.BulkInsert(dataToInsert, batchSize);

Для того, чтобы сделать вставку, необходимо вызвать метод WriteToServer у класса SqlBulkCopy. У него есть несколько перегрузок:


мы будем использовать метод, который принимает IDataReader. У интерфейса IDataReader очень много методов, но нам необходимо только 4. Создадим класс реализующий этот интерфейс. Методы, которые нам нужны пометим как  abstract, остальные как virtual, они нам не нужны и я их не буду показывать в коде.

public abstract class SqlBulkCopyReader : IDataReader
{
     public abstract bool Read();
     public abstract object GetValue(int i);
     public abstract int GetOrdinal(string name);
     public abstract int FieldCount { get; }

     #region // Not required ...
     ....
     #endregion 
}

Отнаследуем класс от SqlBulkCopyReader:

public class LinqEntityDataReader<T> : SqlBulkCopyReader where T : class
{
     private DataTable _sourceTable;
     private readonly IEnumeratort<T> _enumerator;
     private DataRow _current;

     public LinqEntityDataReader(IEnumerable<T> source)
     {
           MapTableName();
           MapColumns();
           _enumerator = source.GetEnumerator();
     }

     public string DestenationTable 
     {
          get { return _sourceTable.TableName; }
     } 

     public IEnumerable<string> Columns
     {
          get
          {
               foreach (DataColumn column in _sourceTable.Columns)
               {
                    yield return column.ColumnName;
               }
           }
      }

      public override object GetValue(int columnIndex)
      {
           return _current[columnIndex];
      }

      public override int GetOrdinal(string name)
      {
           return _sourceTable.Columns[name].Ordinal;
      }

      public override bool Read()
      {
           bool next = _enumerator.MoveNext();

           if (next)
           {
                _current = _sourceTable.NewRow();

                foreach (DataColumn column in _sourceTable.Columns)
                {
                     _current[column] = typeof(T).GetProperty(column.ColumnName)
                           .GetValue(_enumerator.Current, null);
                }
           }

           return next;
     }

     public override int FieldCount
     {
          get { return _sourceTable.Columns.Count; }
     } 

     private void MapTableName()
     {
          Type entityType = typeof(T);

          TableAttribute destenationTable = entityType
              .GetCustomAttributes(typeof(TableAttribute), false)
              .Cast<TableAttribute>()
              .SingleOrDefault();

          if (destenationTable == null)
          {
               throw new ArgumentNullException("destenationTable");
          }

          _sourceTable = new DataTable(destenationTable.Name);
     }

     private void MapColumns()
     {
          Type entityType = typeof(T);
          int columnIndex = 0;

          foreach (PropertyInfo property in entityType.GetProperties())
          {
               ColumnAttribute column = property
                   .GetCustomAttributes(typeof(ColumnAttribute), false)
                   .Cast<columnattribute>()
                   .SingleOrDefault();
          }

          if (column == null)
          {
               return;
          }

          if (!column.IsVersion && !column.DbType.Contains("IDENTITY") && !column.IsDbGenerated)
          {
               _sourceTable.Columns.Add(column.Name ?? property.Name);
          }

          columnIndex++;
     }
}

Начнем с конструктора. Для начала мы должны знать куда нам вставлять данные. Это мы можем узнать у сущности из аттрибута Table, что и происходит в методе MapTableName. Затем нам нужно знать в какие колонки можно вставлять данные. Во вставке могу участвовать лубые колонки кроме IDENTITY, TIMESTAMP и тп. Это можно узнать у свойства сущности, которое помечено аттрибутом Column, что и происходит в методе MapColumns.
SqlBulkCopy начинает свою работу с получения количества колонок, которые участвую во вставке и если указан мапинг, то вызывается метод GetOrdinal, чтоб получить индекс каждой колонки по имени.
Затем SqlBulkCopy идет построчно вызывая метод Read, а потом вызывает GetValue для каждой колонки у текущей записи.
Мое "ноу-хау" - это получение значений колонок рефлексией, когда запрашивается очередная строка, и сохранение этих значений в DataRow. Во-первых это проще, во-вторых читабельней и понятней, а в-третьих - думаете рефлексия в методе Read будет все тормозить ? - Нет! Получилось только быстрей, на удивление (может кто-то скажет почему ?).

Ну и завершающий этап, сама обвертка над SqlBulkCopy в виде Extenstion Method для таблицы-сущности:

public static class TableExtension
{
 public static void BulkInsert<T>(this Table<T> entity, IEnumerable<T> data, int batchSize) 
  where T : class
 {
  LinqEntityDataReader<T> reader = new LinqEntityDataReader<T>(data);

  using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DBContext.ConnectionString))
  {
   foreach (string columnName in reader.Columns)
   {
    sqlBulkCopy.ColumnMappings.Add(columnName, columnName);
   }

   sqlBulkCopy.BatchSize = batchSize;
   sqlBulkCopy.DestinationTableName = reader.DestenationTable;
   sqlBulkCopy.WriteToServer(new LinqEntityDataReader<T>(data));
   sqlBulkCopy.Close();
  }
 }
}

Ну тут вроде ничего военного, все должно быть ясно. Ну и в результате:

IEnumerable<Customer> dataToInsert = GenerateCustomers(100000);
int batchSize = 5000;
context.Customers.BulkInsert(dataToInsert, batchSize);

Ну и как я обещал грубый тест на производительность. Тест производился на вставке 100 000 записей. BatchSize я подобрал оптимальную для данного количества записей. Забыл сказать, что от этого значения зависит много, вы можете как выиграть в производительности, так и не очень выиграть. Для того чтобы подобрать оптимальное значение надо поэксперементировать. Рекомендации мелкософта не помогли, все зависит от ситуации. Для создания фейковых данных использовался метод:

public static IEnumerable<Customer> GenerateCustomers(int count)
{
 for (int i = 0; i < count; i++)
 {
  yield return new Customer
              {
           CustomerID = i.ToString("00000"), 
   CompanyName = "Company" + i, 
   ContactName = "Frederique Citeaux",
                        ContactTitle = "Marketing Manager",
   Address = "24, place Kleber",
   Phone = "(604) 555-4729",
   Region = "WA"
         };
 }
}

Вот что у меня получилось:

  • Мое решение ~ 8 секунд
  • Решение из пример ~ 11секунд
  • Решение из пример + динамический вызов GetValue, который ему посоветовали в коментах ~ 9.5 секунд
  • Стандартный InsertAllOnSubmit ~ 1 минута 23 секунды
Я не думаю, что это совпадение, все-таки 100 000 данных - это немало. Почему так, я еще не узнал.
В любой случае я получил, что хотел. В моем конкретном случае мапинг всех товаров на категорию (10 000 записей) теперь выполняется ~ 1.1 секунды. Думаю этот пример вас когда-то спасет.

суббота, 7 ноября 2009 г.

LINQ dynamic expressions. WHERE <T> IN <Filters>. Part2 - Improvements

В предыдущей статье мы рассмотрели как можно создать аналог SQL опператора WHERE IN.
В результате мы получили LINQ выражение в вид:

string[] filters = new [] { "WA", "SP", "BC" };
var customers = context.Customers.In(p => p.Region, filters);

В читабильности мы не достигли идеального результат, но Restuta предложил решение, как можно его улучшить.

Благодарности:
     Спасибо Restuta за предложенное улучшение.

Рассмотри что изменилось.

Для начала, нам необходимо создать вспомогательный класс, который, как сказал Restuta, надо "пропихнуть"... Наш класс будет выглядеть так:

public class QueryableProperty<T, TKey>
{
     /// <summary>Gets or sets the original query.</summary>
     /// <value>The query.</value>
     public IQueryable<T> Query { get; set; }

     /// <summary>Gets or sets the column to filter.</summary>
     /// <value>The column.</value>
     public Expression<Func<T, TKey>> Column { get; set; }
}

Затем нам необходим вспомогательный Extenstion Method, который как раз и будет "пропихивать" поле, которое мы хотим фильтровать и оригинальный запрос. Этот Extenstion Method как раз и является тем самым улучшением, который придает желаемой читабельности.

/// <summary>Aggregates property for future WHERE IN clause.</summary>
/// <typeparam name="T">The result entity type.</typeparam>
/// <typeparam name="TKey">The type of column to filter.</typeparam>
/// <param name="query" />The original query.</param>
/// <param name="column" />The column to filter.</param>
public static QueryableProperty<T, TKey> WhereProperty<T, TKey>(this IQueryable<T> query, Expression<Func<T, TKey>> column)
{
     return new QueryableProperty<T, TKey> { Query = query, Column = column };
}

Extenstion Method 'IN' почти не изменился, я не буду приводить его код, покажу только сигнатуру, по которой все станет ясно.

public static IQueryable<T> In<T, TKey>(this QueryableProperty<T, TKey> queryableProperty, IEnumerable<TKey> filters)

В результате, наше вырежание приняло следующий вид:

string[] filters = new [] { "WA", "SP", "BC" };
var customers = context.Customers
     .WhereProperty(p => p.Region)
     .In(filters)
     .OrderBy(p => p.Region);

Получилось гламурненько. Как вы видите, цепочка вызовов не прерывается. Это самое главное. После In мы опять возвращаем IQueryable<T> и можем продолжать использовать другие Extension Methods.
Мы, конечно, можем обратиться к оригинальному запросу и после WhereProperty через свойство Query, но WhereProperty предназначался не для того.

Мы добились желаемого результат \m/

воскресенье, 1 ноября 2009 г.

LINQ dynamic expressions. WHERE <T> IN <Filters>

В LINQ есть конструкция, которая позволяет делать SQL запрос с выражением IN. Все примеры я буду приводить на всем известной базе данных Northwind.

Например чтоб получить запрос на SQL в виде


SELECT c.* 
FROM Customers c
WHERE c.Region IN ("WA", "SP", "BC")

нужно написать на LINQ следующий код

string[] filters = new [] { "WA", "SP", "BC" };
context.Customers.Where(p => filters.Contains(p.Region));

Данная конструкция нечитабельная, лично меня она напрягает. Мне хотелось бы чтоб она выглядела более естественно, как то так:

string[] filters = new [] { "WA", "SP", "BC" };
context.Customers.Where(p => p.Region.In(filters));

Конечно так написать нельзя и данных код нескомпилируется, но сделать запрос более естественным можно. Нам помогут Expression Trees, примеры которых вы могли видеть в предыдущих статьях. Далее будет рассказано, как сделать код вида:

string[] filters = new [] { "WA", "SP", "BC" };
var customers = context.Customers.In(p => p.Region, filters);

Погуглив, я нашел пример в котором делается примерно тоже самое, только проще и с обманом. В моем случае, на выходе мы получим SQL запрос с конструкцией WHERE IN, а в примере получается … AND (c.Region = “WA” OR c.Region = “SP” …). Я думаю парень не выдержал, задача все-таки нелегкая. Я тоже понервничал пока разобрался как транслировать в конструкцию IN. Вам будет проще :)

Начнем.
Ниже приведен код “In” Extenstion method’a и подробное его описание по шагам.

public static class LinqExtensions
{
     /// <summary>
     /// Filters the specified query with input filters.
     /// </summary>
     /// <typeparam name="T">The result entity type.</typeparam>
     /// <typeparam name="TKey">The type of column to filter.</typeparam>
     /// <param name="query" />The query.</param>
     /// <param name="column" />The column.</param>
     /// <param name="filters" />The in sequence.</param>
     public static IQueryable<T> In<T, TKey>(
          this IQueryable<T> query, 
          Expression<Func<T, TKey>> column,
          IEnumerable<TKey> filters)
     {
          MethodInfo containsMethod = typeof(System.Linq.Enumerable)
               .GetMethods()
               .Where(m => m.Name == "Contains" && m.GetParameters().Length == 2)
               .Single();

          containsMethod = containsMethod.MakeGenericMethod(new[] { typeof(TKey) });

          List<expression> values = new List<expression>();
          foreach (TKey value in filters)
          {
               values.Add(Expression.Constant(value));
          }

          NewArrayExpression filtersArray = Expression.NewArrayInit(typeof(TKey), values);

          List<expression> arguments = new List<expression>();
          arguments.Add(filtersArray);
          arguments.Add(column.Body);

          MethodCallExpression containsMethodCall = Expression.Call(containsMethod, arguments.ToArray());

          LambdaExpression lambda = Expression.Lambda(containsMethodCall, column.Parameters.ToArray());

          var result = Expression.Call(
               typeof(Queryable),
               "Where",
               new Type[] { query.ElementType },
               query.Expression,
               lambda);

          return query.Provider.CreateQuery<T>(result);
     }
}

Рассмотрим подробней:

  • Сначала нам надо найти метод Contains (17-20). Поскольку этот метод является Extenstion Method, то его нужно искать в классе, в котором хранятся эти расширения. В данном случае System.Linq.Enumerable. Методов Contains два:

    Contains>TSource<(IEnumerable>TSource<, TSource)
    Contains>TSource<(IEnumerable>TSource<, TSource, IEqualityComparer>TSource<) 
    

    нам нужен [1] с двумя параметрами.
  • После того как мы его нашли, у нас будет нетипизированный метод в виде Contains[TSource], а нам нужен Contains, который работает с типом поля, которое нам передается. В данном примере мы фильтровали по Region у которого тип System.String. Делаем с общего метода, типизированный (23). В результате получаем сигнатуру вида Contains<String>(<IEnumerable<String>, String>).
  • Затем нам нужен список значений, по которым будет фильтроваться выборка. Для этого создаем набор констант (24-28) и помещаем их в массив (30). В результате мы получаем динамическое выражение вида new[] { “WA”, “BC”, “SP”}.
  • Согласно сигнатуре метода Contains [1] мы должны передать в него набор фильтров (33) и параметр-поле (34), по которым будет фильтроваться выборка. Делаем вызов метода Contains (36). В результате мы получаем динамическое выражение вида new[] { “WA”, “BC”, “SP”}.Contains(p.Region). Тут мы как раз делаем саму IN конструкцию.
  • После этого нам нужно создать лямбда выражение (38), которое будет передаваться в конструкцию Where. В результате мы получаем динамическое выражение вида p => new[] { “WA”, “BC”, “SP”}.Contains(p.Region). Входящий параметр лямбда выражения будет таким, как вы его назовете при использовании нашего “In” Exstension method. В данном примере он называется p, т.к. я делал вызов вида context.Customers.In(p => p.Region, filters).
  • Все. Нам осталось добавить в существующую цепочку вызовов наш метод Where (40-45).

Думаю вы почерпнулия для себя что-то новое и полезное.