artembaikuzin
commented
6 years ago Искал решение проблемы долгих запросов, если в выдаче имеется поле LineNumbers.ProtocolId:
https://github.com/GolosMovement/StatElectGenerator/issues/86#issuecomment-440567953
Проблема была в том, что СУБД сканировала всю таблицу LineNumbers (использовался clustered index scan, т.е. проход по таблице, как она располагается на носителе - по PK) не используя созданный специально для этого nonclustered индекс на ProtocolId. Поэтому запросы хоть как-то связанные с ProtocolId были медленные. Чтобы такого не было при joined запросах, нужно дополнительно создавать nonclustered index на поле join + include на ProtocolId:
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolIdValue]
ON [dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId],[Value])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)Value добавил для оптимизации запросов вычисления динамических пресетов.
В итоге, провел тесты на dev машине и на staging. Входные данные такие:
staging:
Protocols: 1 291 308
LineNumbers: 23 127 818
dev:
Protocols: 2 094 299
LineNumbers: 42 389 640Тестировал на Президент РФ 2018, QA#3. Вычисляемая формула взята с "потолка", вычисляет что-то с большим количеством LineDescription. Можно поправить на реальную, но сути это не изменит. Выборка по всем протоколам выборов.
dev
MAX-CASE
SELECT
valuesQuery.*,
(valuesQuery.[529] +
valuesQuery.[530] -
valuesQuery.[531] +
valuesQuery.[532] +
valuesQuery.[533] -
valuesQuery.[534] +
valuesQuery.[535] -
valuesQuery.[536]) / (NULLIF(valuesQuery.[526], 0.0) * 1.33) AS Result
FROM (
SELECT
baseQuery.ProtocolId,
MAX(CASE WHEN baseQuery.LineDescriptionId = 526 THEN baseQuery.Value END) AS [526],
MAX(CASE WHEN baseQuery.LineDescriptionId = 529 THEN baseQuery.Value END) AS [529],
MAX(CASE WHEN baseQuery.LineDescriptionId = 530 THEN baseQuery.Value END) AS [530],
MAX(CASE WHEN baseQuery.LineDescriptionId = 531 THEN baseQuery.Value END) AS [531],
MAX(CASE WHEN baseQuery.LineDescriptionId = 532 THEN baseQuery.Value END) AS [532],
MAX(CASE WHEN baseQuery.LineDescriptionId = 533 THEN baseQuery.Value END) AS [533],
MAX(CASE WHEN baseQuery.LineDescriptionId = 534 THEN baseQuery.Value END) AS [534],
MAX(CASE WHEN baseQuery.LineDescriptionId = 535 THEN baseQuery.Value END) AS [535],
MAX(CASE WHEN baseQuery.LineDescriptionId = 536 THEN baseQuery.Value END) AS [536]
FROM (
SELECT
LineNumbers.LineDescriptionId,
LineNumbers.ProtocolId,
LineNumbers.Value
FROM LineNumbers
WHERE LineNumbers.LineDescriptionId in (526, 529, 530, 531, 532, 533, 534, 535, 536)
) AS baseQuery
GROUP BY baseQuery.ProtocolId
) AS valuesQuery
ORDER BY valuesQuery.ProtocolId
-- Elapsed time: 00:00:01.3906250
PIVOT
SELECT
baseQuery.*,
(baseQuery.[529] +
baseQuery.[530] -
baseQuery.[531] +
baseQuery.[532] +
baseQuery.[533] -
baseQuery.[534] +
baseQuery.[535] -
baseQuery.[536]) / (NULLIF(baseQuery.[526], 0.0) * 1.33) AS Result
FROM (
SELECT ProtocolId,
[526], [529], [530], [531], [532], [533], [534], [535], [536]
FROM
(
SELECT LineNumbers.Value,
LineNumbers.LineDescriptionId,
LineNumbers.ProtocolId
FROM LineNumbers
WHERE LineNumbers.LineDescriptionId in (526, 529, 530, 531, 532, 533, 534, 535, 536)
) AS sourceQuery
PIVOT
(
MAX(Value) FOR LineDescriptionId IN ([526], [529], [530], [531], [532], [533], [534], [535], [536])
) AS pivotQuery
) AS baseQuery
ORDER BY baseQuery.ProtocolId
-- Elapsed time: 00:00:01.3437500
staging
MAX-CASE
SELECT
valuesQuery.*,
(valuesQuery.[219] +
valuesQuery.[220] -
valuesQuery.[221] +
valuesQuery.[222] +
valuesQuery.[223] -
valuesQuery.[224] +
valuesQuery.[225] -
valuesQuery.[226]) / (NULLIF(valuesQuery.[216], 0.0) * 1.33) AS Result
FROM (
SELECT
baseQuery.ProtocolId,
MAX(CASE WHEN baseQuery.LineDescriptionId = 216 THEN baseQuery.Value END) AS [216],
MAX(CASE WHEN baseQuery.LineDescriptionId = 219 THEN baseQuery.Value END) AS [219],
MAX(CASE WHEN baseQuery.LineDescriptionId = 220 THEN baseQuery.Value END) AS [220],
MAX(CASE WHEN baseQuery.LineDescriptionId = 221 THEN baseQuery.Value END) AS [221],
MAX(CASE WHEN baseQuery.LineDescriptionId = 222 THEN baseQuery.Value END) AS [222],
MAX(CASE WHEN baseQuery.LineDescriptionId = 223 THEN baseQuery.Value END) AS [223],
MAX(CASE WHEN baseQuery.LineDescriptionId = 224 THEN baseQuery.Value END) AS [224],
MAX(CASE WHEN baseQuery.LineDescriptionId = 225 THEN baseQuery.Value END) AS [225],
MAX(CASE WHEN baseQuery.LineDescriptionId = 226 THEN baseQuery.Value END) AS [226]
FROM (
SELECT
LineNumbers.LineDescriptionId,
LineNumbers.ProtocolId,
LineNumbers.Value
FROM LineNumbers
WHERE LineNumbers.LineDescriptionId in (216, 219, 220, 221, 222, 223, 224, 225, 226)
) AS baseQuery
GROUP BY baseQuery.ProtocolId
) AS valuesQuery
ORDER BY valuesQuery.ProtocolId
-- Elapsed time: 00:00:18.3564453
PIVOT
SELECT
baseQuery.*,
(baseQuery.[219] +
baseQuery.[220] -
baseQuery.[221] +
baseQuery.[222] +
baseQuery.[223] -
baseQuery.[224] +
baseQuery.[225] -
baseQuery.[226]) / (NULLIF(baseQuery.[216], 0.0) * 1.33) AS Result
FROM (
SELECT ProtocolId,
[216], [219], [220], [221], [222], [223], [224], [225], [226]
FROM
(
SELECT LineNumbers.Value,
LineNumbers.LineDescriptionId,
LineNumbers.ProtocolId
FROM LineNumbers
WHERE LineNumbers.LineDescriptionId in (216, 219, 220, 221, 222, 223, 224, 225, 226)
) AS sourceQuery
PIVOT
(
MAX(Value) FOR LineDescriptionId IN ([216], [219], [220], [221], [222], [223], [224], [225], [226])
) AS pivotQuery
) AS baseQuery
ORDER BY ProtocolId
-- Elapsed time: 00:00:15.8750000
Итог
Запросы по иерархии с 97699 записей с динамическими пресетами работают 1-2 секунды на dev машине (40 миллионов записей в LineNumbers). На staging (а значит и production) это длится 15-20 секунд (23 миллиона записей в LineNumbers). Нужно использовать метод PIVOT, т.к. на staging он быстрее, когда как на dev разницы нет.
Возможно, на реальном приложении запросы будут еще быстрее, т.к. время на непосредственную передачу данных между приложением и БД будет меньше.
В любом случае, azure db сейчас работает на порядок медленнее чем dev машины на которых идет разработка. Без оптимизационного индекса для ProtocolId запросы MAX-CASE/PIVOT занимают 10-11 секунд на dev машине, на staging их невозможно будет дождаться.
Реализация пресетов несложна, если мы обуславливаемся использовать в формуле NULLIF функцию для выражения в знаменателе, чтобы отсеять ошибку деления на 0. Так нам не придется писать парсер формул, достаточно будет просто подменять идентификаторы LineDescription как это происходит сейчас.
В итоге формулы станут, например, выглядеть так: ([555] + [999]) / NULLIF([123] + [321], 0) * 100.0.
BlackWrangler
Функция PIVOT строит простую и удобную таблицу за 50 секунд для 100 000 записей. Таблица может быть global temporary или обычной. Разница только в "##" ('CalcTbl' + convert(nvarchar,@PrSetID) или ('##CalcTbl' + convert(nvarchar,@PrSetID)
Если global temporary будет быстро умирать, можно пользоваться постоянной. Размер - 10 Мб для самых больших выборов. Индексов, кажется, нет.
С помощью этой расчетной таблицы, как временной, так и реальной scatter-plot рассчитывается за 15 секунд, histogram - 24 секунды на 100 000 записей. Большая часть времени уходит на построение иерархии.
Это примитивными SQL query кусками найдеными в Интернете. Наверно, RDM сможет лучше. Формулы, кажется, абсолютно аналогичны задаваемыми при настройке пресетов.
Построение расчетной таблицы можно сделать гибким, не зависящим от конкретных выборов. Был бы только ProtocolSetID (в скрипте - @PrSetID)
Скрипт для построения global temporary:
Для постоянной: убрать ## и использовать
IF OBJECT_ID('CalcTbl' + convert(nvarchar,@PrSetID), 'U') IS NULLПолучается табличка типа:
Скаттер-плот (15 секунд)
Гистограмма (20-24 секунды)
Всё это исследование заняло 3 часа.