В жизни множества приложений наступает такой момент, когда наскоро внедренное решение перестает удовлетворять требованиям банальной пригодности к использованию, вследствие отсутствия оптимизации. Наступил такой момент и для нашего приложения на ASP.NET Core и Vue.JS с TypeScript, когда количество записей в таблицах, откуда мы берем данные для страниц с более чем 20 динамическими фильтрами, перевалило за несколько миллионов. Резко встал вопрос об оптимизации, о которой далее и пойдет речь.

Начало

Первым дело было решено разобрать, что же такого есть на страницах, что типичные, казалось бы, запросы к серверу не успевают выполниться. Было выделено 2 основных блока, в которых и обнаружились проблемы: секция со списком быстрых фильтров и секция с записями.

Секция с фильтрами представляет собой несколько блоков с фиксированным списком опций, условие по каждой из которых можно быстро добавить в запрос, поставив галочку. Для каждой из них также загружается количество записей, подходящих под выбранные фильтры и по другим атрибутам. Таким образом определилось первое потенциальное проблемное место: чем больше опций для фильтров, тем больше идет запросов количества записей, исполнение каждого из которых, если учесть количество подтягиваемых таблиц и их размер, может занять значительное время.

Секция с записями включает в себя как, собственно, отобранные записи, так и ещё одну порцию фильтров, но уже без возможности быстро добавить в условие какую-либо опцию и без необходимости подсчета количества подходящих сущностей. Таблица с записями довольно стандартная, без изысков, а данные, конечно, не отображаются всем скопом, а разбиты на страницы. На первый взгляд ничего криминального, но стоило залезть под капот, как стала очевидной причина медленной работы: запрос составлялся через LINQ без учета возможности его грамотной конвертации в запрос SQL, записи из используемых таблиц загружались в оперативную память, создавая экземпляры сущностей, после чего уже отбирались по указанным атрибутам, подвергались сортировке и отбору требуемого количества. Такая же процедура проделывалась и для подсчета количества записей. Также я выяснил, что вследствие использования определенной библиотеки для отрисовки и манипуляции таблицей уже в клиентском приложении запросы на получение записей и их количества при загрузке страницы отправлялись несколько раз, по сути создавая бесполезную нагрузку на сервер.

Фронт работ был определен. Требовалось либо сократить количество, либо полностью искоренить дублирование запросов с клиента, разобраться с тем, как работает блок с фильтрами с фиксированным набором опций, и, конечно, исправить принцип создания запроса и загрузки данных для таблицы уже на сервере.

Решение проблемы фильтров с фиксированными опциями

Принцип их работы прост. Идет запрос на сервер с указанием, какие фильтры выбраны пользователем. Там идет последовательный перебор атрибутов, указанных в блоке, и их опций, добавление этих опций в условие запроса подсчета количества записей и исполнение этого запроса. Т.е. один запрос с клиента влечет за собой N запросов к базе данных, где N - количество опций. Очевидно, без оптимизации этих самых запросов и при подобном подходе с ростом количества возможных опций ситуация будет ухудшаться, поэтому было принято решение посылать запросы с клиента последовательно для каждой опции. Для того, чтобы грамотно организовать очередность, решили использовать давно доказавшую свою полезность библиотеку RxJS.

Её прелесть в том, что она помогает легко и без особых взрывов мозга представить многие явления как потоки событий и предоставляет множество способов с этими потоками взаимодействовать. Объединение, фильтрация, конвертация, задание расписания… В данном случае понадобилось задание очередности и возможность прервать исполнение потока по требованию. Суть примененного метода проста: после исполнения запроса на получение записей для основной таблицы, либо после изменения фильтров ожидаем буквально 3 секунды, чтобы пользователь точно прекратил настраивать условия, после чего добавляем вызов запроса на получение количества записей для каждой из опций в очередь и начинаем по этой очереди идти. Как только один запрос завершается, начинает исполняться другой, и так до самого конца очереди. Если в то время, пока очередь ещё не опустела, пользователь изменит каким-нибудь образом условие выборки, все исполняющиеся запросы останавливаются, очередь очищается и заполняется заново, дабы избежать необходимости совершения бесполезных запросов.

// заведем по два read-only потока и по потоку для передачи информации
// об обновлениях фильтров фиксированных и динамических
private changesStream: Subject<number> = new Subject<number>();
private changesSubscription?: Subscription;

private filtersChanged: Subject<void> = new Subject<void>();
private filtersQueue?: Subscription;

// при загрузке страницы добавляем обработчики событий из потоков
// в данном случае мы просто добавляем задержку 0.7 секунды между совершением
// события и стартом реакции на него, в данном случае загрузкой и заполнением
// таблицы
this.changesSubscription = this.changesStream.pipe(
      debounceTime(700)
).subscribe(() => this.reloadData());

// на событие изменения динамических фильтров мы реагируем с задержкой в 3
// секунды, за которые пользователь предположительно должен успеть ввести все
// требуемые условия. 
// По истечению 3-х секунд мы получаем список запросов на получение количества
// записей для каждой опции и подменяем этим списком текущую очередь, если она
// есть, с помощью метода switchMap
this.filtersQueue = this.filtersChanged.pipe(
      debounceTime(3000),
      switchMap(ev => from(this.getCountRequestObservables()))
).subscribe();

getCountRequestObservables из кода выше возвращает массив read-only потоков, в основе каждого из которых лежит запрос к серверу на получение количества записей для определенной опции.

Метод сработал хорошо, на слабо заполненных базах нужные данные подтягиваются мгновенно, на более заполненных очередность видна невооруженным взглядом, но даже такой результат можно считать успешным, т.к. он позволил значительно минимизировать затраты серверных ресурсов.

Решение проблемы с запросом записей для таблицы

Упомянутый выше способ не сработал бы в полной мере, останься основной запрос таким же медленным и требовательным к памяти. Самой серьёзной загвоздкой в его оптимизации являлось то, что на один и тот же атрибут пользователь мог добавить несколько условий, т.е., к примеру, он мог задать поиск по имени не только "Дмитрий", но и "Дмитрий", "Олег", "Егор", а мог не задать и ничего, при этом поиск по каким-то опциям необходимо было иметь в том числе и по частичному соответствию. Возможности задать через LINQ подобное условие не было, поэтому пришлось обратиться к старым-добрым выражениям.

Для каждого из задействованных атрибутов была создана коллекция выражений со значением фильтра, будь то сравнение или поиск в строке, после чего все полученные выражения, коих могли быть десятки, объединялись в одно древо и применялись к запросу. Это позволило динамически во время исполнения запроса с клиента строить запрос к базе данных, полностью соответствующий заданным пользователем фильтрам. Метод сработал лучше, чем предполагалось, единственной проблемой стали поля сущностей, тип которых был Nullable, поэтому к ним не получалось применить методы их внутренних типов.

//
// Метод получения выражения для условия полного равенства значения поля
// сущности, обращение к которому хранится в memberExpression, и значений из
// переданного списка.
// Если значение для сравнения передано одно, то возвращаем выражение с вызовом
// метода Equal. Если больше одного, то вызовы метода Equal для каждого из
// значений объединяем с помощью OrElse.
// По ходу составления приводим тип значений из списка к типу поля.
//
internal static Expression GetEqualComparingExpression<T>(MemberExpression memberExpression, IList<T> values)
{
  int valuesCount = values.Count();

  if (valuesCount == 1)
  {
    return Expression.Equal(memberExpression, Expression.Convert(Expression.Constant(values.First()), memberExpression.Type));
  }

  if (valuesCount > 1)
  {
    if (memberExpression.Type != typeof(T))
    {
      // В случае, если значения для сравнения имеют тип, отличный от типа поля, значения которого будем сравнивать, то
      // попробуем конвертировать значения в тип, соответствующий типу поля
      MethodInfo convertingMethod = typeof(QueryExpressionFactory).GetMethod(nameof(ConvertList)).MakeGenericMethod(memberExpression.Type, typeof(T));
      object list = convertingMethod.Invoke(typeof(QueryExpressionFactory), new[] { values });
      return Expression.Call(Expression.Constant(list), list.GetType().GetMethod("Contains", new[] { memberExpression.Type }), memberExpression);
    }
    return Expression.Call(Expression.Constant(values), typeof(List<T>).GetMethod("Contains", new[] { typeof(T) }), memberExpression);
  }

  // Если список вариантов пуст, то возвращаем заранее неисполнимое условие (0 == 1), чтобы не было выбрано ни единой записи
  return Expression.Equal(Expression.Constant(0), Expression.Constant(1));
}

//
// Для того, чтобы компилятор завершил работу успешно, пришлось приведение
// типа значений из списка к требуемому типу вынести в отдельный обобщенный
// метод
//
private static List<TResult> ConvertList<TResult, TOrigin>(IList<TOrigin> values)
{
  var convertedList = new List<TResult>();

  foreach (TOrigin value in values)
  {
    convertedList.Add(ChangeType<TResult>(value));
  }

  return convertedList;
}

//
// Метод для конвертации каждого отдельного элемента из списка, причем мы
// добавили проверку на то, что конвертируем только типы из Nullable в конечные
//
private static TResult ChangeType<TResult>(object value)
{
  Type resultType = typeof(TResult);

  if (resultType.IsGenericType && resultType.GetGenericTypeDefinition() == typeof(Nullable<>))
  {
    if (value == null)
    {
      return default;
    }

    resultType = Nullable.GetUnderlyingType(resultType);
  }

  return (TResult)Convert.ChangeType(value, resultType);
}

//
// Метод получения выражения сравнения части значения поля с любой из переданных
// строк.
// Если значение для сравнения передано одно, то возвращаем выражение с вызовом
// метода Contains. Если больше одного, то вызовы метода Contains для каждого из
// значений объединяем с помощью OrElse.
// В данном случае приведение типов уже не требуется.
//
internal static Expression GetStringContainsComparingExpression(MemberExpression memberExpression, IEnumerable<string> values)
{
  int valuesCount = values.Count();

  Type stringType = typeof(string);

  if (valuesCount == 1)
  {
    return Expression.Call(memberExpression, stringType.GetMethod("Contains", new[] { stringType }), Expression.Constant(values.First()));
  }
  if (valuesCount > 1)
  {
    var comparingExpressions = new List<MethodCallExpression>();
    foreach (string value in values)
    {
      comparingExpressions.Add(Expression.Call(memberExpression, stringType.GetMethod("Contains", new[] { stringType }), Expression.Constant(value)));
    }
    BinaryExpression binaryExpression = Expression.OrElse(comparingExpressions[0], comparingExpressions[1]);

    if (valuesCount > 2)
    {
      for (int i = 2; i < comparingExpressions.Count(); i++)
      {
        binaryExpression = Expression.OrElse(binaryExpression, comparingExpressions[i]);
      }
    }

    return binaryExpression;
  }

  // Если список вариантов пуст, то возвращаем заранее неисполнимое условие (0 == 1), чтобы не было выбрано ни единой записи
  return Expression.Equal(Expression.Constant(0), Expression.Constant(1));
}

После того, как для каждого поля, участвующего в условии отбора, созданы выражения, мы объединяем их и получаем конечное выражение, которое можно подставить в метод IQueryable Where и которое будет корректно конвертировано в SQL.

//
// Метод объединения коллекции выражений сравнения в одно выражение.
// Если коллекция содержит только одно выражение, возвращаем его. Если
// несколько, то объединяем их все с помощью метода AndAlso.
//
internal static Expression<Func<T, bool>> CreateConditionalExpression<T>(ParameterExpression baseExpression, IList<Expression> expressions) where T : Entity
{
	Expression<Func<T, bool>> conditionsPredicate = null;
	int filtersCount = expressions.Count();
 
  if (filtersCount > 0)
  {
  	if (filtersCount == 1)
    {
    	conditionsPredicate = Expression.Lambda<Func<T, bool>>(expressions.First(), baseExpression);
    }
    else
    {
    	BinaryExpression filtersPredicate = Expression.AndAlso(expressions[0], expressions[1]);
    	if (filtersCount > 2)
    	{
    		for (int i = 2; i < expressions.Count(); i++)
    		{
    			filtersPredicate = Expression.AndAlso(filtersPredicate, expressions[i]);
    		}
    	}
    	conditionsPredicate = Expression.Lambda<Func<T, bool>>(filtersPredicate, baseExpression);
    }
  }
    
  return conditionsPredicate;
}

Помимо замены составления запроса с LINQ на выражения требовалось перенести сортировку записей и разбиение на страницы тоже на этап запроса, иначе все проделанные махинации остались бы незамеченными. Если с указанием количества требуемых записей и размером пропускаемых проблем не возникло, то сортировка из коробки работала не особо хорошо. Используя утилиту SQL Server Profiler, мы выяснили, что именно на неё уходила львиная доля времени запроса. Сортировка записей возможна по нескольким параметрам, преимущественно строковым, а не по ключам, хранящимся в разных связанных таблицах. Решено было создать индексы на каждое из полей, по которым можно проводить сортировку. MS SQL не разрешает создать некластерный индекс по строковому полю с максимально возможной длиной, поэтому длину значений некоторых полей пришлось ограничить, дабы индексацию провернуть.

Успех был ощутимым: загрузку страницы удалось ускорить с бесконечности до 0.5-1.5 секунд на запрос без дополнительных фильтров. При задании дополнительных условий время может как увеличиваться, так и уменьшаться, но все ещё оставаться в рамках приемлемого. То, что запрос начал получаться оптимальным, проверили через тот же SQL Server Profiler, с помощью которого можно убедиться, что вместо нескольких частичных запросов совершается один полноценный.

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

Итоги

Очевидно, здесь ещё есть, над чем работать. Как минимум можно заменить классическую пагинацию, реализованную через LIMIT и OFFSET, на LIMIT и условие по ID. Возможно, можно произвести какие-нибудь махинации на уровне базы данных. На данный момент это решение выглядит удовлетворяющим всем требованиям. Есть уверенность, что до тех пор, пока количество записей не вырастет миллионов до 10, дополнительная оптимизация не понадобится.

Предлагаю обсудить примененное решение и возможные улучшения в комментариях к посту.