Здравствуйте, elmal, Вы писали:
E>Есть следующая проблема. Есть одна достаточно большая таблица. Допустим Invoice. К ней соотношением 1 ко многим соединены другие таблицы, например Event и ServiceDistribution
Непонятно, что значит "1 ко многим". В примере почему-то приведено наоборот — "многие к одному", т.к. джойн идёт по primary key основной таблицы, а не по её foreign keys.
Если речь об обычной звезде, то известные мне оптимизаторы тупят с переупорядочиванием группировки и джойна. Точнее, неспособны понять суть primary key.
Давай я приведу более простой и понятный пример того, как переписываются star-join-aggregate запросы, может быть поможет и в этом случае:
-- 1: таблицаcreate table Invoices
(
id int identity primary key,
cityID int not null foreign key references city(id),
managerId int not null foreign key references manager(id),
amount numeric(10,4) not null
)
-- 2: наивный запрос:select city.Name, manager.Name, sum(i.amount) as totals
from Invoices i
inner join city on city.id = i.cityId
inner join manager on manager.id = i.managerId
group by city.Name, manager.Name order by city.Name, manager.Name
-- 3: нормальный запросselect city.Name, manager.Name, Totals
from (select cityId, managerId, sum(i.amount) as Totals from Invoices i group by cityId, managerId) i -- выполняем группировку по интересующим нас foreign keysinner join city on city.id = i.cityId
inner join manager on manager.id = i.managerId
order by city.Name, manager.Name
Между этими запросами есть разница — она проявляется при наличии "однофамильцев". Но, во-первых, это легко исправить, выполнив группировку ещё раз во внешнем запросе — там обычно result set уже на порядок меньше, и это почти ничего не стоит. Во-вторых, зачастую нам именно это и надо, и исходный запрос выглядит примерно так (ох, сколько я такого кода переписал в своё время за индусами...):
select city.ID, city.Name, manager.ID, manager.Name, sum(i.amount) as totals
from Invoices i
inner join city on city.id = i.cityId
inner join manager on manager.id = i.managerId
group by CityId, city.Name, manager.ID, manager.Name
order by city.Name, manager.Name
Вот тут бы как раз оптимизатору сообразить, что у нас группировка по любому атрибуту после первичного ключа уже ничего не сгруппирует, и что можно безопасно переставлять джойн наружу group by...
Уыы, увы и ах. Проносите группировку внутрь руками.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, elmal, Вы писали: E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
все обозримые базы, за исключением ранних версий mysql
поддерживают такие оптимизации
декартово произведение будет только в случае если выбран nested loop для join
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, sereginseregin, Вы писали:
S>>Зачем нужна сумма sum(sd.Amount) и группировка по e.EventKindID ??? E>Это просто пример запроса, хоторый может понадобиться. Задачу в общем случае я описал в http://rsdn.org/forum/db/7098710
Запрос явно не оптимизирован, нужен не пример запроса, а пример задачи под которую нужен запрос.
В Вашем примере вначале декартово произведение, потом группировка. Нужно наоборот — группировка, потом соединение.
В если абстрактно у вас звезда, то вначале делаете отдельные подзапросы с группировкой по каждому лучу звезды, а затем соединяете их полученные данные через JOIN.
Здравствуйте, elmal, Вы писали:
E>Есть следующая проблема. Есть одна достаточно большая таблица. Допустим Invoice. К ней соотношением 1 ко многим соединены другие таблицы, например Event и ServiceDistribution
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации, позволяющие избавиться от декартового произведения большого количества огромных таблиц и объединяя их в одну? Или черт с ним с SQL — есть ли вообще решения, ориентированные на запросы подобного рода?
Давайте начнем с того, что чего вы используете эту схему — для транзакционной системы или для расчетов, группировок и репортинга?
Если это не транзакционная система, работающая чисто на выборку данных, то надо написать хорошее, денормализованное хранилище со star schema, без кубов. Далее при дизайне нужно партиционировать его в зависимости от того, как идёт работа с датами (чаще всего репортаж всегда месячные, квартальные или годовые), а возможно и не по дате, а ещё по чему-то. Создавайте агрегированные датамарты (агрегировать сумму, которую вы делаете в запросе, должен за вас ETL и всего один раз).
С моей точки зрения, дело совсем не в базе, а именно в дизайне, вам нужен грамотный архитектор хранилищ.
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, Milena, Вы писали:
M>>Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions. E>В случае денормализации там будет настолько огроменная табличка, что там вагон винчестеров потребуется. Потому пытаемся сделать решение, которое работает как OLAP, но над нормализованными таблицами и в реалтайме, а не путем препроцессинга. Кстати небезуспешно, для ряда задач такое получается, а всякие консультанты нам говорили что нам кластер из 1000 машин нужен, иначе никак.
Про кластер такого размера — это, конечно, чересчур, однако если почитать и подумать, никто и не делает полную денормализацию. Вам нужны только таблицы фактов, где чаще всего не более ста колонок, и таблицы изменений. Таблицы фактов процесятся один раз и дальше хранят данные разных уровней агрегации. При этом вы же можете партиционировать и горизонтально, и вертикально для оптимизации производительности.
К примеру, у меня сейчас в одном датамарт есть таблица детального уровня — да, большая, денормализованная, но в ней в разы меньше колонок, через если я буду джойнов исходные данные в транзакционной системе, далее дневная агрегация и месячная агрегация. В зависимости от того, какой репорт запрошен, работает одна из этих трёх таблиц, и весьма быстро.
Здравствуйте, elmal, Вы писали:
E>Семантика может быть любой.
По-моему, в этом ваша ошибка. Решение не может быть одновременно универсальным и максимально эффективным. Бизнесу не нужны "любые запросы" в любых разрезах. Вам нужно поглубже проанализировать потребности бизнеса и решать конкретные задачи. Я, конечно, могу ошибаться, но на первый взгляд ваша ситуация выглядит именно так, я с таким сталкивался.
Есть следующая проблема. Есть одна достаточно большая таблица. Допустим 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 — есть ли вообще решения, ориентированные на запросы подобного рода?
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, Вы писали:
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.
По идее оптимизатор должен понимать и делать все оптимально сам. Он на деле не так плохо и делает, там нет декартового произведения полного. Он сначала бегает по лучу звезды, объединяет с инвооисом, далее результат объединяется с другой таблицей. Но один черт это недостаточно шустро.
?
columnstore уже сделано. Ибо без этого с производительностью полный швах. На деле, что хочется:
Нужно как то сказать оптимизатору, чтобы:
1) он перестроил данные таким образом, чтобы пробегаться только по таблице Invoice. Связи 1 ко многим от таблиц звезда он как то хафигачил в столбцы массив. Это на стадии подготовки данных;
2) Далее сделать обычную группировку по таблице Invoice, но в агрегаторе делать пробег по каждому массиву, делать над ним быстрый мини groupBy и класть результат в группу, связанную с Invoice
3) Преобразовать хитрую группировку и аггрегацию на шаге 2 к требуемому выводу, это достаточно тривиально.
Собственно это я и хотел сделать явно в стартовом сообщении.
Как это сказать оптимизатору — хз. Потому сейчас мысли написать подобную хрень самому, по идее это должно работать на порядки быстрее, при том, что обычный ручной лисапед уже написан и группировки делает примерно с той же скоростью (даже быстрее), что MS SQL на том же железе, его нужно только доработать.
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, sereginseregin, Вы писали:
E> в подзапросе при группировке пропадает InvoiceId, по которому нужно джоинить.
Так в подзапросе по InvoiceId и надо группировать
Мне не совсем ясен смысл одновременно отображать по sd.GroupServiceTypeID и e.EventKindID, если на один i.InvoiceID будет два e.EventKindID, то и подсчет суммы sum(sdt.Amount) задвоится, если окажется три e.EventKindID — соответсвенно затроится.
Вместе эти три таблицы могут отображаться, но нужно понять саму прикладную задачу.
Здравствуйте, Milena, Вы писали:
M>Давайте начнем с того, что чего вы используете эту схему — для транзакционной системы или для расчетов, группировок и репортинга?
Для расчетов, сводящихся к группировке и аггрегациям. M>С моей точки зрения, дело совсем не в базе, а именно в дизайне, вам нужен грамотный архитектор хранилищ.
Дело естественно не в базе. Это тот же самый вопрос, который я задавал в http://rsdn.org/forum/db/7091963.1
Здравствуйте, sereginseregin, Вы писали:
S>Так в подзапросе по InvoiceId и надо группировать
Там де факто уже сгруппировано, там уменьшение данных идет максимум процентов на 10, там время выполнения наоборот увеличится.
S>Вместе эти три таблицы могут отображаться, но нужно понять саму прикладную задачу.
Прикладная задача — это делать максимально быстро подобные запросы. Задача не стоит сделать один конкретный запрос, задача стоит сделать что то вроде OLAP на объемах, которые фиг сделаешь препроцессингом из за слишком большого количества dimensions и объема данных, максимально быстро вертеть все возможные данные любой организации. И фиг с ним что задваивается затраиваются, это можно нормализовать делением если возникнет необходимость.
Здравствуйте, elmal, Вы писали:
E>Здравствуйте, Milena, Вы писали:
M>>Давайте начнем с того, что чего вы используете эту схему — для транзакционной системы или для расчетов, группировок и репортинга? E>Для расчетов, сводящихся к группировке и аггрегациям. M>>С моей точки зрения, дело совсем не в базе, а именно в дизайне, вам нужен грамотный архитектор хранилищ. E>Дело естественно не в базе. Это тот же самый вопрос, который я задавал в http://rsdn.org/forum/db/7091963.1
. Базовое решение у нас уже есть, пока на базе классического OLAP. Но этого недостаточно, продумываем лучшие варианты.
Я поняла вопрос, просто вы спросили, существуют ли базы, которые умеют работать быстро, поэтому я вам и ответила, что дело не в RDBMS.
Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions.
Здравствуйте, Milena, Вы писали:
M>Я поняла вопрос, просто вы спросили, существуют ли базы, которые умеют работать быстро, поэтому я вам и ответила, что дело не в RDBMS. M>Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions.
В случае денормализации там будет настолько огроменная табличка, что там вагон винчестеров потребуется. Потому пытаемся сделать решение, которое работает как OLAP, но над нормализованными таблицами и в реалтайме, а не путем препроцессинга. Кстати небезуспешно, для ряда задач такое получается, а всякие консультанты нам говорили что нам кластер из 1000 машин нужен, иначе никак.
Здравствуйте, Milena, Вы писали:
M>Про кластер такого размера — это, конечно, чересчур, однако если почитать и подумать, никто и не делает полную денормализацию. Вам нужны только таблицы фактов, где чаще всего не более ста колонок, и таблицы изменений. Таблицы фактов процесятся один раз и дальше хранят данные разных уровней агрегации. При этом вы же можете партиционировать и горизонтально, и вертикально для оптимизации производительности.
Проблема в том, что таблицы фактов невозможно препроцессить. Мы попробовали посчитать для упрощенной задачи сколько будет записей в объединенной таблице чтобы удовлетворить требованиям. Получилось 150 триллионов записей . И это минимум половина того, что нужно сделать. Те, кто занимался другой половиной, подобные приколы даже не пытались считать. И по этой таблице нужно построить куб на порядка 1000 dimensions . Но это ладно, это уже практически решенная задача, хоть я и боялся что не получится это сделать физически на приемлемом количестве нод. Далее уже дело техники. Просто если б задача решалась стандартными средствами, нас бы не привлекали. Более того, задача параллельно решается стандартными OLAP средствами, хоть и долго, нас привлекают чтоб посчитать с нужным уровнем детализации.
Правда это немного другая задача, чем здесь, текущая будет попроще, потому и хотелось бы попробовать ее сделать более менее стандартными средствами, а не хреначить свою базу данных с блекджеком и т.д и корячиться с ручными оптимизациями.
Попытаться применить стандартные средства, а не сразу отвергать в пользу уже написанного одного низкоуровневого решения — это кстати именно моя идея, ибо не люблю работу ради работы . Ибо текущее решение есть, оно работает неплохо, но оно низкоуровневое и все оптимизации, которые уже давно есть в стандартных средствах, приходится делать вручную, затачиваясь на конкретную задачу, за счет чего и обеспечивая скорость. Остальные уже заранее отвергнули стандартные решения, ибо попытались и не вышло ни черта. Пытались на Microsoft стеке, если что. Соответственно сейчас находимся в фазе ресерча, рассматриваем все варианты.
И дополнительно — даже таблицы изменений могут оказаться в нашем случае огромными. В сильно упрощенном случае задача решена, средствами кажется как раз MS Analysis Services. Проблема в том, как сделать задачу не в сильно упрощенном, а именно в желаемом виде.
E>Проблема в том, что такой запрос на миллиардных таблицах будет выполняться непозволительно медленно. И если подсоединять еще таблицы и по их полям группировать, то деградация производительности будет экспоненциальная — из за декартового произведения. Это полная чушь, декартово произведение — используется в т.н. star-join optimizations, и только https://msdn.microsoft.com/en-us/library/gg567299.aspx, чтобы вытащить блум-фильтры на таблицу фактов.
На DW нагрузках зависимость в целом вполне себе линейная.
E>Соответственно вопрос — есть ли такие базы, которые поддерживают подобный SQL или выполняющие подобные оптимизации
Все из большой тройки выполняют подобные оптимизации из коробки.
Здравствуйте, Sinclair, Вы писали:
S>Непонятно, что значит "1 ко многим". В примере почему-то приведено наоборот — "многие к одному", т.к. джойн идёт по primary key основной таблицы, а не по её foreign keys.
Именно 1 ко многим. В данном примере это означает, что одной Invoice соответствует много CityID и ManagerID. То есть в Invoices не будет этих полей, но они будут в таблицах City и Manager будет поле InvoiceID.
Здравствуйте, elmal, Вы писали: S>>Непонятно, что значит "1 ко многим". В примере почему-то приведено наоборот — "многие к одному", т.к. джойн идёт по primary key основной таблицы, а не по её foreign keys. E>Именно 1 ко многим. В данном примере это означает, что одной Invoice соответствует много CityID и ManagerID.
Что у нас является primary key в таблицах ServiceDistributionInvoice, Event, invoice?
Как определены foreign key?
E>То есть в Invoices не будет этих полей, но они будут в таблицах City и Manager будет поле InvoiceID.
Тогда непонятна семантика запроса.
Вот у меня есть invoice с некоторой суммой, который привязан к десяти "городам".
У городов есть классифицирующий признак — ну, например, регион. Связь — многие-к-одному, то есть в одном регионе много городов. Пусть у нас сумма инвойса = 100р, в регионе 1 — три города, в регионе 2 — семь городов.
Берём, считаем "сумму" по регионам:
select city.RegionID, sum(i.Amount) from city
inner join Invoice i on city.invoiceID = i.ID
group by city.RegionID
Получим что-то типа
id
-- -----
1 300
1 700
Физический смысл этого от меня ускользает.
Можно посчитать это чуть быстрее, если сделать
select c.regionId, invoiceCount*i.Amount as Totals from
(select invoiceId, regionId, count(*) as invoiceCount from city group by invoiceId, regionId) c
inner join invoice i on i.id = c.invoiceId
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, Sinclair, Вы писали:
S>Что у нас является primary key в таблицах ServiceDistributionInvoice, Event, invoice?
Поле id.
S>Как определены foreign key?
Стандартным образом поля ServiceDistributionInvoice.invoiceId и Event.invoiceId являются внешними ключами, связанными с Invoice.invoiceId
E>>То есть в Invoices не будет этих полей, но они будут в таблицах City и Manager будет поле InvoiceID. S>Тогда непонятна семантика запроса.
Семантика может быть любой. Если к одной накладной привязано много городов, это может означать что затраты накладной нужно разносить на разные города. В оригинальном примере — одной накладной соответствует много (ну не совсем много, а порядка 10 максимум) услуг и много событий (тоже порядка 10 максимум), связанной с накладной. И нужно сгруппировать накладные по конкретным услугам, каждой услуге может быть привязана доля затрат, которая в сумме сходится к 1, и эту сумму после группировки нужно умножить на затраты на 1 накладную. Это просто как пример, на деле там что угодно может быть.
Или пример — накладная эта кассовый ордер. В чеке пришло 100 рублей прибыли. У меня 10 работников — уброщицы, кассир, грузчик и т.д. С ними договорились, что им не будут платить зарплату а они будут получать определенный процент от прибыли. И я хочу в запросе получить информацию сколько мне заплатить уборщицу и грузчику, если их доля в прибыли 5 процентов. Одновременно у меня куча разных услуг, и у них тоже доли уже в собестоимости. И я, как дворник, хочу поинтересоваться, как изменилась бы моя зарплата в проглом месяце если бы мы смогли перестроить услуги. И такое может хотеть уборщица, кассир, менеджер, топ менеджер и т.д.
Здравствуйте, elmal, Вы писали:
E>Семантика может быть любой.
План запроса может оказываться разным, в зависимости от желаемой семантики.
E>Если к одной накладной привязано много городов, это может означать что затраты накладной нужно разносить на разные города.
В зависимости от того, как именно разносить, мы строим конкретные агрегатные функции, и применяем различные порядки группировок/джойнов.
E>В оригинальном примере — одной накладной соответствует много (ну не совсем много, а порядка 10 максимум) услуг и много событий (тоже порядка 10 максимум), связанной с накладной. И нужно сгруппировать накладные по конкретным услугам, каждой услуге может быть привязана доля затрат, которая в сумме сходится к 1, и эту сумму после группировки нужно умножить на затраты на 1 накладную.
Ну вот в исходном примере этого нет, из-за этого непонятно, что можно оптимизировать.
Крайне желательно показать пример данных. Раз у нас везде "многие к одному" — давайте начнём с ровно одной накладной. Заполним списки услуг и событий для неё, и покажем, какой выхлоп ожидается при выполнении этого запроса.
E>Или пример — накладная эта кассовый ордер. В чеке пришло 100 рублей прибыли. У меня 10 работников — уброщицы, кассир, грузчик и т.д. С ними договорились, что им не будут платить зарплату а они будут получать определенный процент от прибыли. И я хочу в запросе получить информацию сколько мне заплатить уборщицу и грузчику, если их доля в прибыли 5 процентов. Одновременно у меня куча разных услуг, и у них тоже доли уже в собестоимости. И я, как дворник, хочу поинтересоваться, как изменилась бы моя зарплата в проглом месяце если бы мы смогли перестроить услуги. И такое может хотеть уборщица, кассир, менеджер, топ менеджер и т.д.
Ну, пока понятно, что ничего не понятно
Нужен наглядный пример. Потому что без него и так понятно, что нужно просто умножить прибыль чека на 0.05 чтобы получить "сколько мне заплатить уборщице и грузчику", безо всякой агрегации.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, wildwind, Вы писали:
W>По-моему, в этом ваша ошибка. Решение не может быть одновременно универсальным и максимально эффективным. Бизнесу не нужны "любые запросы" в любых разрезах. Вам нужно поглубже проанализировать потребности бизнеса и решать конкретные задачи. Я, конечно, могу ошибаться, но на первый взгляд ваша ситуация выглядит именно так, я с таким сталкивался.
Так на этом и пытаемся играть, что запросы будут определенного вида к данным определенного вида — одна основная таблица и есть дополнительно поля, содержащие многие (порядка 10 значений одновременно). Что интересно, самописный прототип показывает весьма неплохую скорость, по крайней мере MS SQL делает по скорости на порядок, причем еще на худшем железе. Не бесплатно это все конечно, возрастает время разработки, но зато есть полное понимание как это работает внутри, полное понимание что является узким местом и что можно улучшить, возможность делать различные ручные оптимизации, нормальный язык программирования, профайлер и т.д.
Здравствуйте, elmal, Вы писали:
E>…ну и естественно фильтрацию, но фильтрация уменьшает объем данных, потому худшим случаем будет отсутствие фильтрации
Бывает и наоборот на большущих как раз данных: снова всё зависит от того, какие есть индексы. Поиск может занять заметное время, большее чем требуется на агрегацию. Поэтому к запросам надо давать скрипт создания таблиц и план выполнения.
Help will always be given at Hogwarts to those who ask for it.
Здравствуйте, Milena, Вы писали:
M>Если это не транзакционная система, работающая чисто на выборку данных, то надо написать хорошее, денормализованное хранилище со star schema, без кубов…
Но это может добавить некоторую задержку между созданием данных и доступностью их в аналитике. А топикстартеру хочется онлайновости. Или есть варианты сделать такое без ощутимых временных потерь?
Help will always be given at Hogwarts to those who ask for it.
. Потому пробуем что то подобное сделать в рантайме.
