Есть следующая проблема. Есть одна достаточно большая таблица. Допустим Invoice. К ней соотношением 1 ко многим соединены другие таблицы, например Event и ServiceDistribution
И нужно быстро выполнять запросы вида:
SELECT
i.SenderDivisionID,
sd.GroupServiceTypeID,
e.EventKindID,
sum(sd.Amount)
FROM [Fc2].[dbo].[Invoice] i
inner join [Fc2].[dbo].[ServiceDistributionInvoice] sd on sd.InvoiceID = i.InvoiceID
inner join [Fc2].[dbo].[Event] e on e.InvoiceID = i.InvoiceID
GROUP BY
i.SenderDivisionID,
sd.GroupServiceTypeID,
e.EventKindID
Проблема в том, что такой запрос на миллиардных таблицах будет выполняться непозволительно медленно. И если подсоединять еще таблицы и по их полям группировать, то деградация производительности будет экспоненциальная — из за декартового произведения.
Теоретически запросы подобного рода можно было бы выполнять более эффективно, если не делать декартового произведения. Запрос мог бы быть что то вроде следующего:
SELECT
i.SenderDivisionID,
toArray(
SELECT(sd.GroupServiceTypeID)
FROM i INNER JOIN [Fc2].[dbo].[ServiceDistributionInvoice] sd on sd.InvoiceID = i.InvoiceI
),
toArray(
SELECT (e.EventKindID)
FROM i INNER JOIN [Fc2].[dbo].[Event] e on e.InvoiceID = i.InvoiceID)
FROM Invoice i
GROUP BY
i.SenderDivisionID
Если бы подобные запросы поддерживались, то была бы теоретическая возможность выполнять из максимально быстро. И если бы нужно было сделать еще группировки по другим полям таблиц, связанную отношением 1 ко многим, то время выполнения бы не увеличивалось катастрофически.
Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Здравствуйте, elmal, Вы писали:
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Я подзабыл, да и не особо глубоко знаю MSSQL. Но мне кажется, правильно настроенные констрэйнты и индексы по полям, которые используются в join, должны ускорить процесс.
Поправьте, если не прав.
Здравствуйте, elmal, Вы писали: E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
все обозримые базы, за исключением ранних версий mysql
поддерживают такие оптимизации
декартово произведение будет только в случае если выбран nested loop для join
Здравствуйте, elmal, Вы писали:
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Postgresql поддерживает toArray. В Oracle тоже наверняка должно быть подобное, у них там вообще всё есть.
Здравствуйте, Mihas, Вы писали:
M>Я подзабыл, да и не особо глубоко знаю MSSQL. Но мне кажется, правильно настроенные констрэйнты и индексы по полям, которые используются в join, должны ускорить процесс. M>Поправьте, если не прав.
Так индексы по полям, которые используются в join, естественно есть, без этого вообще хрен дождешься результата. Какие индексы еще поставить чтоб работало шустро — хз. И дополнительно, нужно как то сделать так, чтоб максимально быстро выполнялись вообще все возможные запросы подобного вида, так что конкретный индекс под один запрос не получится. Чтоб не 3 таблицы, а 10 или 20. Ключевое — одна таблица основная, остальные таблицы связаны с ней 1 ко многим отношением.
Здравствуйте, torvic, Вы писали:
T>все обозримые базы, за исключением ранних версий mysql T>поддерживают такие оптимизации T>декартово произведение будет только в случае если выбран nested loop для join
Что то непохоже. Если брать отдельные запросы подобного рода, но для двух таблиц (Invoice и Event или Invoice и ServiceDistribution), то запросы выполняются максимум за 4 секунды. А когда 3 таблицы — уже 42 секунды. 4 уже не дождаться, не говоря уже про 10. MS SQL 2017.
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, torvic, Вы писали:
T>>все обозримые базы, за исключением ранних версий mysql T>>поддерживают такие оптимизации T>>декартово произведение будет только в случае если выбран nested loop для join E>Что то непохоже. Если брать отдельные запросы подобного рода, но для двух таблиц (Invoice и Event или Invoice и ServiceDistribution), то запросы выполняются максимум за 4 секунды. А когда 3 таблицы — уже 42 секунды. 4 уже не дождаться, не говоря уже про 10. MS SQL 2017.
А что в плане? Смотрите что ему не хватает. Возможно индексы не используются.
Здравствуйте, elmal, Вы писали:
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Пол описанию это похоже на OLAP с классической схемой "звезда".
Не смотрели в сторону MS Analysis Services или длугих OLAP решений?
Здравствуйте, BlackEric, Вы писали:
BE>В MS SQL можно попробовать сделать Materialized view. Но данные займут много места
Это точно не реально. Ибо это сработает для одного конкретного запроса, а подобных запросов требуется очень много. Там одних сырых данных без индексов на 100 гигов и это просто прототип для тестов. И при запросах нужно прошерстить именно все эти 100 гигов, точнее каждую запись каждой таблицы, но не все поля.
Здравствуйте, vmpire, Вы писали:
V>Пол описанию это похоже на OLAP с классической схемой "звезда". V>Не смотрели в сторону MS Analysis Services или длугих OLAP решений?
Это почти OLAP и есть. Проблема в том, что этот MS Analysis Services не может построить даже упрощенный куб на гораздо меньшей размерности, чем требуется, просто физически. За год может и построит наверное, если купить пару вагонов винчестеров . Потому пробуем что то подобное сделать в рантайме.
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, BlackEric, Вы писали:
BE>>В MS SQL можно попробовать сделать Materialized view. Но данные займут много места E>Это точно не реально. Ибо это сработает для одного конкретного запроса, а подобных запросов требуется очень много. Там одних сырых данных без индексов на 100 гигов и это просто прототип для тестов. И при запросах нужно прошерстить именно все эти 100 гигов, точнее каждую запись каждой таблицы, но не все поля.
А) Column Store + тюнинг индексов.
Б) Кластер с распараллеливанием поиска по каким либо диапазонам
Что бы порекомендовать что-то детальнее нужно детально представлять задачу.
Как я понимаю многие [ServiceDistributionInvoice] и многие [Event] ссылаются на один Invoice, думаю тут какая-то мешанина в запросе. Зачем нужна сумма sum(sd.Amount) и группировка по e.EventKindID ??? В подобных случаях [ServiceDistributionInvoice] и [Event] собирают и группируют в подзапросе в JOIN, что поддерживает большинство СУБД SQL. Может объясните суть запроса, чтоб его правильно перестроить?
Здравствуйте, BlackEric, Вы писали:
BE>А) Column Store + тюнинг индексов. BE>Б) Кластер с распараллеливанием поиска по каким либо диапазонам BE>Что бы порекомендовать что-то детальнее нужно детально представлять задачу.
Кластер то понятно, нужна максимальная производительность на одной ноде.
Задача следующая. Есть одна большая базовая таблица порядка миллиарда записей. Она является центром звезды, то есть к ней связано множество таблиц соотношением 1 ко многим.
Нужно выполнять над такой хреновиной произвольные запросы на группировку и аггрегацию в реальном времени (ну и естественно фильтрацию, но фильтрация уменьшает объем данных, потому худшим случаем будет отсутствие фильтрации). Группировка и аггрегация может быть не только по полям центральной базовой таблицы звезды, но и по любому полю из связанных столбцов.
Классический OLAP предрассчитанный куб не построить физически. Потому нужно как то выполнять нужные запросы на группировку и агрегацию в рантайме.
Здравствуйте, sereginseregin, Вы писали:
S>Зачем нужна сумма sum(sd.Amount) и группировка по e.EventKindID ???
Это просто пример запроса, хоторый может понадобиться. Задачу в общем случае я описал в http://rsdn.org/forum/db/7098710
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, sereginseregin, Вы писали:
S>>Зачем нужна сумма sum(sd.Amount) и группировка по e.EventKindID ??? E>Это просто пример запроса, хоторый может понадобиться. Задачу в общем случае я описал в http://rsdn.org/forum/db/7098710
Запрос явно не оптимизирован, нужен не пример запроса, а пример задачи под которую нужен запрос.
В Вашем примере вначале декартово произведение, потом группировка. Нужно наоборот — группировка, потом соединение.
В если абстрактно у вас звезда, то вначале делаете отдельные подзапросы с группировкой по каждому лучу звезды, а затем соединяете их полученные данные через JOIN.
Здравствуйте, elmal, Вы писали:
V>>По описанию это похоже на OLAP с классической схемой "звезда". V>>Не смотрели в сторону MS Analysis Services или длугих OLAP решений? E>Это почти OLAP и есть. Проблема в том, что этот MS Analysis Services не может построить даже упрощенный куб на гораздо меньшей размерности, чем требуется, просто физически. За год может и построит наверное, если купить пару вагонов винчестеров . Потому пробуем что то подобное сделать в рантайме.
Тогда может и правда попробовать columnstore, как уже предложили
Здравствуйте, sereginseregin, Вы писали:
S>В если абстрактно у вас звезда, то вначале делаете отдельные подзапросы с группировкой по каждому лучу звезды, а затем соединяете их полученные данные через JOIN.
Вот только не очень понимаю как написать подобный оптимизированный запрос.
Насколько я понимаю, имелось в виду что то вроде:
SELECT
i.SenderDivisionID,
sdt.GroupServiceTypeID,
et.EventKindID,
sum(sdt.Amount)
FROM
[Fc2].[dbo].[Invoice] i,
inner join (
SELECT sd.InvoiceID, sd.GroupServiceTypeID
FROM [Fc2].[dbo].[ServiceDistributionInvoice]
GROUP BY sd.GroupServiceTypeID
) sdt on sdt.InvoiceID = i.InvoiceID
inner join (
SELECT e.InvoiceID, e.EventKindID
[Fc2].[dbo].[Event] e
GROUP BY e.EventKindID
) et on et.InvoiceID = i.InvoiceID
GROUP BY
i.SenderDivisionID
Но это полная хрень и этот запрос работать не будет (он в текущем виде даже не корректен), ибо в подзапросе при группировке пропадает InvoiceId, по которому нужно джоинить.
Как сделать запрос чтоб сначала была группировка, и можно было бы результать приджоинить по invoiceId мне не очень понятно. Как и не очевидно, как это ускорит, ибо вложенные запросы с группировкой возвращают огромное количество значений, там вернутся вообще все invoiceId.
По идее оптимизатор должен понимать и делать все оптимально сам. Он на деле не так плохо и делает, там нет декартового произведения полного. Он сначала бегает по лучу звезды, объединяет с инвооисом, далее результат объединяется с другой таблицей. Но один черт это недостаточно шустро.
Здравствуйте, elmal, Вы писали:
E>Есть следующая проблема. Есть одна достаточно большая таблица. Допустим Invoice. К ней соотношением 1 ко многим соединены другие таблицы, например Event и ServiceDistribution
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Давайте начнем с того, что чего вы используете эту схему — для транзакционной системы или для расчетов, группировок и репортинга?
Если это не транзакционная система, работающая чисто на выборку данных, то надо написать хорошее, денормализованное хранилище со star schema, без кубов. Далее при дизайне нужно партиционировать его в зависимости от того, как идёт работа с датами (чаще всего репортаж всегда месячные, квартальные или годовые), а возможно и не по дате, а ещё по чему-то. Создавайте агрегированные датамарты (агрегировать сумму, которую вы делаете в запросе, должен за вас ETL и всего один раз).
С моей точки зрения, дело совсем не в базе, а именно в дизайне, вам нужен грамотный архитектор хранилищ.
?
columnstore уже сделано. Ибо без этого с производительностью полный швах. На деле, что хочется:
Нужно как то сказать оптимизатору, чтобы:
1) он перестроил данные таким образом, чтобы пробегаться только по таблице Invoice. Связи 1 ко многим от таблиц звезда он как то хафигачил в столбцы массив. Это на стадии подготовки данных;
2) Далее сделать обычную группировку по таблице Invoice, но в агрегаторе делать пробег по каждому массиву, делать над ним быстрый мини groupBy и класть результат в группу, связанную с Invoice
3) Преобразовать хитрую группировку и аггрегацию на шаге 2 к требуемому выводу, это достаточно тривиально.
Собственно это я и хотел сделать явно в стартовом сообщении.
Как это сказать оптимизатору — хз. Потому сейчас мысли написать подобную хрень самому, по идее это должно работать на порядки быстрее, при том, что обычный ручной лисапед уже написан и группировки делает примерно с той же скоростью (даже быстрее), что MS SQL на том же железе, его нужно только доработать.
. Потому пробуем что то подобное сделать в рантайме.
