Можно ли ускорить полезный запрос в 300 раз?

Back

Дано:
– крупная аптечная сеть;
– таблица чековых строк - данные за 10 лет работы;
– измерение  чеков - 93 миллиона записей.

Собран чековый куб, с показателями оборотов (штуки, рубли, суммы скидок), количеством чеков (мера не суммирования, а количества уникальных значений, иными словами distinct count), есть вычисляемый средний чек. Поскольку группа мер оборотов и группа мер количества чеков построена поверх таблицы чековых строк, а привязки идут прямые к дате, номенклатуре, подразделению, дисконтной карте, кассовой смене – то можно накладывать фильтры не только на однозначно связанные с чеком измерения (подразделение, дата, смена), но и связанные со строками чеков измерения (номенклатура).

Задача – сделать агрегированный OLAP-куб и подсчитать там обороты и количество чеков по данным последних 4 лет, выкинув на лету обороты тех чеков, которые по государственным программам, в которых участвует аптечная сеть, получились со 100% скидкой. Данные посчитать в разрезе дат и подразделений.

Конечно, сделана view поверх таблицы чековых оборотов, затем нарезана на 4 секции последних лет, в кубе точно так же созданы 2 группы мер – обороты (рубли, штуки, количество строк) и количество чеков (все тот же счетчик distinct count по ID чека).

Решение:

Обрабатываем куб и видим – секция оборотов, в которой несколько показателей, для одного года занимает 3 Мбайта, а секция количества чеков – 70 Мбайт. Даже с учетом того, что тип столбца с ID чека у нас не integer, рекомендованный Microsoft, а BigInt, что в 2 раза больше, все равно – явный перебор.

Строим отчет по иерархии дат в строках. Показатели чеков по оборотам – меньше 1 секунды, как только добавляем количество чеков или производный от него средний чек – 30 секунд. Как такое может быть? Дисковая подсистема у нас такова, что позволят читать все 4 служебные файла по 70 Мбайт каждый за секунду. Да и это только первое чтение, а далее данные должны быть в кэше OLAP базы.

Что же происходит оставшиеся 29 секунд? Запускаем профайлер и видим – секция каждого года для счетчика distinct count сканируется ровно столько раз, сколько дней в этом году. Иными словами, 365 раз по 10-20 мс. Для каждой из 4 годовых секций. А затем на одном ядре процессора OLAP служба начинает складывать количество чеков методом не сложения, а подсчета уникальных значений.

1. Меняем view для годовых секций. Выбираем явным образом сумму оборотов в рублях, в штуках, сумму количества строк и сумму уникальных значений ID чеков как count(distinct ID), группируя по ключам дат и подразделений.

2. Меняем тип количества чеков с distinct count на обычную сумму SUM. Иными словами, заставляем SQL сервер предварительно агрегировать информацию до попадания в куб. Почему мы это можем сделать – да потому что один ID чека может быть только в одной дате и в одном подразделении, привязка прямая и уникальная.

Получаем – годовые секции количества чеков по 450 Кбайт, однократное сканирование каждой секции при построении данного отчета, общее время выполнение – 110 мс, иными словами мы ускорили построение отчетов в 300 раз за счет оптимизации работы куба.

Ответ: Можно.

 

Андрей Кашаверский

Спасибо за проявленный интерес! Интересующие материалы придут вам на указанную почту в течение одного рабочего дня.

Поздравляем, ваша регистрация успешно пройдена!