Здравствуйте, 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

Автор: elmal
Дата: 24.03.18

. Базовое решение у нас уже есть, пока на базе классического OLAP. Но этого недостаточно, продумываем лучшие варианты.

Здравствуйте, elmal, Вы писали:

E>Как это сказать оптимизатору — хз.
На ум приходит только хинт "FORCE ORDER". Но не уверен, что он тут поможет

Здравствуйте, sereginseregin, Вы писали:

S>Так в подзапросе по InvoiceId и надо группировать
Там де факто уже сгруппировано, там уменьшение данных идет максимум процентов на 10, там время выполнения наоборот увеличится.

S>Вместе эти три таблицы могут отображаться, но нужно понять саму прикладную задачу.
Прикладная задача — это делать максимально быстро подобные запросы. Задача не стоит сделать один конкретный запрос, задача стоит сделать что то вроде OLAP на объемах, которые фиг сделаешь препроцессингом из за слишком большого количества dimensions и объема данных, максимально быстро вертеть все возможные данные любой организации. И фиг с ним что задваивается затраиваются, это можно нормализовать делением если возникнет необходимость.

Здравствуйте, elmal, Вы писали:

E>Здравствуйте, Milena, Вы писали:

M>>Давайте начнем с того, что чего вы используете эту схему — для транзакционной системы или для расчетов, группировок и репортинга?
E>Для расчетов, сводящихся к группировке и аггрегациям.
M>>С моей точки зрения, дело совсем не в базе, а именно в дизайне, вам нужен грамотный архитектор хранилищ.
E>Дело естественно не в базе. Это тот же самый вопрос, который я задавал в http://rsdn.org/forum/db/7091963.1

Автор: elmal
Дата: 24.03.18

. Базовое решение у нас уже есть, пока на базе классического OLAP. Но этого недостаточно, продумываем лучшие варианты.

Я поняла вопрос, просто вы спросили, существуют ли базы, которые умеют работать быстро, поэтому я вам и ответила, что дело не в RDBMS.
Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions.

Здравствуйте, Milena, Вы писали:

M>Я поняла вопрос, просто вы спросили, существуют ли базы, которые умеют работать быстро, поэтому я вам и ответила, что дело не в RDBMS.
M>Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions.
В случае денормализации там будет настолько огроменная табличка, что там вагон винчестеров потребуется. Потому пытаемся сделать решение, которое работает как OLAP, но над нормализованными таблицами и в реалтайме, а не путем препроцессинга. Кстати небезуспешно, для ряда задач такое получается, а всякие консультанты нам говорили что нам кластер из 1000 машин нужен, иначе никак.

Здравствуйте, elmal, Вы писали:

E>Здравствуйте, Milena, Вы писали:

M>>Я не совсем понимаю, как у вас может быть OLAP, если запрос, написанный в примере, использует нормализованные таблицы, а в хранилище они должны денормализованны. У вас по идее вообще не должно быть джойнов, кроме как с dimensions.
E>В случае денормализации там будет настолько огроменная табличка, что там вагон винчестеров потребуется. Потому пытаемся сделать решение, которое работает как 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 или выполняющие подобные оптимизации
Все из большой тройки выполняют подобные оптимизации из коробки.


Приложи сюда схему базы ( как делать описано тут https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only)
И xml планы запросов с которыми у тебя проблемы

Здравствуйте, 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 keys
 inner 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...
Уыы, увы и ах. Проносите группировку внутрь руками.

Здравствуйте, 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 чтобы получить "сколько мне заплатить уборщице и грузчику", безо всякой агрегации.

Здравствуйте, elmal, Вы писали:

E>Семантика может быть любой.

По-моему, в этом ваша ошибка. Решение не может быть одновременно универсальным и максимально эффективным. Бизнесу не нужны "любые запросы" в любых разрезах. Вам нужно поглубже проанализировать потребности бизнеса и решать конкретные задачи. Я, конечно, могу ошибаться, но на первый взгляд ваша ситуация выглядит именно так, я с таким сталкивался.

Здравствуйте, wildwind, Вы писали:

W>По-моему, в этом ваша ошибка. Решение не может быть одновременно универсальным и максимально эффективным. Бизнесу не нужны "любые запросы" в любых разрезах. Вам нужно поглубже проанализировать потребности бизнеса и решать конкретные задачи. Я, конечно, могу ошибаться, но на первый взгляд ваша ситуация выглядит именно так, я с таким сталкивался.
Так на этом и пытаемся играть, что запросы будут определенного вида к данным определенного вида — одна основная таблица и есть дополнительно поля, содержащие многие (порядка 10 значений одновременно). Что интересно, самописный прототип показывает весьма неплохую скорость, по крайней мере MS SQL делает по скорости на порядок, причем еще на худшем железе. Не бесплатно это все конечно, возрастает время разработки, но зато есть полное понимание как это работает внутри, полное понимание что является узким местом и что можно улучшить, возможность делать различные ручные оптимизации, нормальный язык программирования, профайлер и т.д.

Здравствуйте, elmal, Вы писали:

E>…ну и естественно фильтрацию, но фильтрация уменьшает объем данных, потому худшим случаем будет отсутствие фильтрации

Бывает и наоборот на большущих как раз данных: снова всё зависит от того, какие есть индексы. Поиск может занять заметное время, большее чем требуется на агрегацию. Поэтому к запросам надо давать скрипт создания таблиц и план выполнения.

Здравствуйте, Milena, Вы писали:

M>Если это не транзакционная система, работающая чисто на выборку данных, то надо написать хорошее, денормализованное хранилище со star schema, без кубов…

Но это может добавить некоторую задержку между созданием данных и доступностью их в аналитике. А топикстартеру хочется онлайновости. Или есть варианты сделать такое без ощутимых временных потерь?