BarsUp.Net - УНП - INквизиция

В нашей работе постоянно приходится сталкиваться с деградацией производительности на некоторых запросах данных... бла-бла-бла.

Начало можно прочитать вот тут, поэтому тратить время не буду. Вкратце содержание предыдущей части - мы глянули на плохой запрос в коде УНП (который возможно написал я сам), и ускорили его с помощью мозга, двух строк кода и какой-то там матери с 22 минут до 4. Ну что, поехали дальше?

Сегодня мы будем бороться с этим же запросом, но взглянем на него с другой стороны. После последних изменений наш дружок выглядит вот так:

Если взглянуть на запросы из предыдущего поста, то мы увидим что под-запрос за информацией об Исполнении (executionQuery) зело убог и страшен, да и вызывается много раз. Поэтому первым делом мы его материализуем в массив идентификаторов и будем использовать в качестве аргументов массив чисел, а не подзапрос. Результат после материализации:

Было:
  eventRisks       : 00:04:00.1174783

Материализация executionQuery:
  onlyApprovedResExecIds : 00:00:00.0326666

Запрос рисков:
  eventRisks       : 00:00:05.9757224

В целом то все, можно считать что это - поебда. Почему ? Потому что в конкретно этом случае вместо вычислений по подзапросу оптимизатору БД удалось найти более эффективный способ работы по массиву констант. В другом случае, например при отсутствии индекса, выборка по IN была бы сильно медленнее.

Давайте поковыряем IN. Рассмотрим запрос по ссылке - выборка Организаций по условию вхождения поля ExternalId в коллекцию идентификаторов.

Проблема при выполнении запроса в том, что индекса по полю ExternalId нет. Мы могли бы добавить индекс в пару кликов, но это скучно и не интересно сейчас.

Сейчас хочется проверить - сможем ли мы используя код построить более эффективный запрос для данной ситуации ?

Поехали!

В текущей ситуации мы используем примерно такой код для получения организаций:

var orgIds = DataStore.GetAll<Organization>().Where(x => externalIds.Contains(x.ExternalId)).Select(x => x.Id).ToArray();

При трансляции linq-выражения в sql запрос для метода Contains используется конструкция IN, и в случае отсутствия индекса мы получаем вот такую картину:

Для ускорения запроса, в нашем случае, мы можем заменить использование IN на exists in values:

exists ( select 1 from (values (v1)...(vn)) v(id) where v.id = org.external_id )

или же заменим IN на exists in unnest(array)

exists ( select 1 from unnest(array[v1...vn]) v where v = org.external_id )

В целом проверка на существование значения поля в заранее определенной коллекции через exists выглядит как будто бы эффективнее, теперь нам нужно добиться генерации этих выражений из linq - вместо исходного выражения мы могли бы использовать что-то вроде

var orgIds = DataStore.GetAll<Organization>()
.Where(x => x.ExternalId.ExistsInValues(externalIds))
.Select(x => x.Id)
.ToArray();

или 

var orgIds = DataStore.GetAll<Organization>()
.Where(x => x.ExternalId.ExistsInUnnest(externalIds))
.Select(x => x.Id)
.ToArray();

Создадим простые extension-методы:

public static bool ExistsInValues(this long value, params long[] values) => values != null && values.Contains(value);

public static bool ExistsInUnnest(this long value, params long[] values) => ExistsInValues(value, values);

После чего нужно подсказать NHibernate как обрабатывать вызовы этих методов, для этого создадим и зарегистрируем HQL-генератор:

Так как наш hql-генератор для простоты формирует вызов несуществующего метода, нужно добавить интерцептор, который подменит этот вызов на реальный sql:

Сейчас мы можем использовать наши новые методы расширения в linq-выражениях, и их вызовы будут корректно транслированы в sql.

Кстати - чтобы проверить какой запрос будет сгенерирован NHibernate для конкретно вашего IQueryable, можно воспользоватся extension-методом ToSql :

Давайте сравним результаты:

Exists In Values : 00:00:00.6723268
Exists In Unnest : 00:00:00.3937588
Contains         : 00:00:03.3370716

В целом, эти методы можно использовать в некоторых случаях - например если индекса по проверяемому полю нет, или запрос в целом сложнее чем просто проверка вхождения в набор значений. Если бы мы сразу накинули индекс на ExternalId, Contains отработал бы быстрее, если бы проверяемых значений (собственно values) было бы не три тысячи, а например всего 5 (просто 5, не пять тысяч) - результаты опять таки были бы другими.

Вобщем нет универсального метода, надо анализировать и вносить изменения по ситуации.

В следующем посте рассмотрим вариант выноса печати отчетов из интерфейса системы в фоновую очередь - это будет первым шагок к выделению отдельного сервиса печати.