Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000.
Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос.
Запрос может содержать критерии (фильтры) количеством от нуля до нескольких десятков (предварительная оценка до 80, но может и более). Критерии могут быть как простые, типа date = currentYear. Так и сложные, типа — рассчитать превышение значения поля над средним значением этого поля за указанный год (N-ое количество записей) и выбрать записи у которых превышение более чем K процентов.
Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
Возможно будет несколько понятнее если я приведу упрощенный пример единичного критерия.
Список финансовых отчетов о доходах по квартально. Выбрать компании у которых прибыль к концу года 2017 года (год выбрал оператор) превысила средне годовую этой компании (по расчетам среднего арифметического квартальных отчетов) на 5 (выбрал оператор) и более процентов.
Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка.
Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения.
Как все это делать?
Здравствуйте, CyberRussia, Вы писали:
CR>Добрый день,
CR>Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
CR>Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000. CR>Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос. CR>Запрос может содержать критерии (фильтры) количеством от нуля до нескольких десятков (предварительная оценка до 80, но может и более). Критерии могут быть как простые, типа date = currentYear. Так и сложные, типа — рассчитать превышение значения поля над средним значением этого поля за указанный год (N-ое количество записей) и выбрать записи у которых превышение более чем K процентов. CR>Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
CR>Возможно будет несколько понятнее если я приведу упрощенный пример единичного критерия. CR>Список финансовых отчетов о доходах по квартально. Выбрать компании у которых прибыль к концу года 2017 года (год выбрал оператор) превысила средне годовую этой компании (по расчетам среднего арифметического квартальных отчетов) на 5 (выбрал оператор) и более процентов.
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка. CR>Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения. CR>Как все это делать?
Я как-то делал похожее, но там все данные легко умещались в памяти обычного компа(2.5 гига), а обновлялись через импорт csv-файла. Работало быстро, может подобный вариант подойдет?
CR>Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать
Если будет выбирать оператор, почему зашкаливать? У тебя вот хватит терпения сто критериев мышкой клавой выбрать?
Жизнь не обязана доставлять удовольствие. Достаточно отсутствия страданий.
Здравствуйте, Qulac, Вы писали: Q>Я как-то делал похожее, но там все данные легко умещались в памяти обычного компа(2.5 гига), а обновлялись через импорт csv-файла. Работало быстро, может подобный вариант подойдет?
Сейчас файл базы данных весит почти 6Гб. И скорее всего в ходе практической эксплуатации объем данных увеличится раза в два.
Здравствуйте, s_aa, Вы писали: _>Если будет выбирать оператор, почему зашкаливать? У тебя вот хватит терпения сто критериев мышкой клавой выбрать?
Строго говоря — выбор оператора это только ближайшая реализация. Следующая — автоматический перебор значений критериев с выбором лучшего результата.
Здравствуйте, CyberRussia, Вы писали:
CR>Как все это делать?
Многое от характера данных зависит. Если они не меняются, а только накапливаются, можешь про лямбда-архитектуру почитать или про olap.
Ещё вариант, "для бедных" — просто кешировать результаты запросов либо наиболее общих и частых подзапросов (напр.ehCache), история кэш-хитов позволит частотную статистику собрать.
Индексы тогда точно не помешают, чтобы вслепую их не ставить, можно их включать-выключать, запуская разные варианты через actual execution plan.
Здравствуйте, gyraboo, Вы писали: G>Многое от характера данных зависит. Если они не меняются, а только накапливаются, можешь про лямбда-архитектуру почитать или про olap.скор
Скорее накапливаются. Попробую почитать, спасибо.
G>Ещё вариант, "для бедных" — просто кешировать результаты запросов либо наиболее общих и частых подзапросов (напр.ehCache), история кэш-хитов позволит частотную статистику собрать.
Про кэш думал. Но сейчас бы понять, как сам запрос правильно составить, учитывая его объем (количество критериев).
Попробуйте сперва начать с linq (linq2db или entity framework). Это будет самый компактный код.
linq2db может сам сгенерировать классы c# для БД.
Для ef вроде надо использовать сторонние утилиты.
linq запросы можно протестировать в linqpad. ef в него интегрирован, linq2db надо подключать по инструкции.
SQL Server по результатам работы может сам подсказать,
где недостает каких индексов (правда тут тоже надо головой думать).
Здравствуйте, B7_Ruslan, Вы писали:
B_R>Здравствуйте, CyberRussia, Вы писали:
B_R>Попробуйте сперва начать с linq (linq2db или entity framework). Это будет самый компактный код.
EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
Здравствуйте, paucity, Вы писали: P>Не очень понял вопрос... Возможно, в сторону OLAP посмотреть стоит?
Вопрос — как создаются запросы динамически в себя включающие десятки критериев (от нуля до нескольких десятков), при этом отдельный критерий это не просто выборка значения поля, а расчетное значение учитывающее другие строки (записи) в БД.
Здравствуйте, CyberRussia, Вы писали:
CR>EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения.
Еще есть ситуация,
когда часть join-ов работает для фильтрации данных (например "найти компанию"), т.е. сокращает выводимые строки,
а другая часть — на загрузку результата (например "доли владения в найденной компании"), размножает найденные строки.
Если эти типы join поместить в один запрос — ничего хорошего не будет.
Здравствуйте, B7_Ruslan, Вы писали:
B_R>Здравствуйте, CyberRussia, Вы писали:
CR>>EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
B_R>К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения. B_R>Еще есть ситуация, B_R>когда часть join-ов работает для фильтрации данных (например "найти компанию"), т.е. сокращает выводимые строки, B_R>а другая часть — на загрузку результата (например "доли владения в найденной компании"), размножает найденные строки.
B_R>Если эти типы join поместить в один запрос — ничего хорошего не будет.
Довольно много критериев типа — посчитать средний доход компании за год (на основе квартальных отчетов) и сравнить с последним квартальным отчетом. Выбрать компании у которых превышение больше на N процентов. То есть один единственный критерий подразумевает для расчетов загрузку нескольких строк (рассчитать среднее) и по результатам только делать вывод о включении / не включении текущей строки в выборку. И подобных критериев десятки рассчитывающих разные значения.
Поэтому я боюсь, что скорость работы будет, мягко говоря, очень низкой, если решать задачу "в лоб".
Здравствуйте, CyberRussia, Вы писали:
CR>Добрый день,
CR>Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
CR>Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000. CR>Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос.
Судя по объему данных и по тому, что нужен один запрос, результат будет использоваться в аналитике — отчёты, загрузка в аналитические приложения и пр.? Если да, то лучше изначально планировать перенос этих данных в хранилище с агрегацией, чтобы не делать сотни джойнов каждый раз.
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка. CR>Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения. CR>Как все это делать?
Можно написать хранимую процедуру с параметризацией запроса. Во-первых, план исполнения будет кэширован, так же как и наиболее часто возвращаемые данные. Во-вторых, можно попробовать создать условия выборки такого плана (это не всегда работает, надо смотреть конкретный случай):
Select ...
From A
Left join B On...
...
Where (A.col_1 = @param_1 or @param_1 IS NULL)
And ...
Если параметр передан — критерий применится, а если пришел пустой — выберутся все значения по данной колонке, как будто параметра нет.
Усложнение возникает, когда есть несколько значений, то это решается через превращение строки в табличку через табличную функцию.
Здравствуйте, Milena, Вы писали: M>Судя по объему данных и по тому, что нужен один запрос, результат будет использоваться в аналитике — отчёты, загрузка в аналитические приложения и пр.? Если да, то лучше изначально планировать перенос этих данных в хранилище с агрегацией, чтобы не делать сотни джойнов каждый раз.
Заказчик хочет сделать аналитику как раз на основе таких запросов. Заказчик в технологиях вообще не разбирается. Да и для меня область незнакомая, не сталкивался раньше.
CR>Довольно много критериев типа — посчитать средний доход компании за год (на основе квартальных отчетов) и сравнить с последним квартальным отчетом. Выбрать компании у которых превышение больше на N процентов. То есть один единственный критерий подразумевает для расчетов загрузку нескольких строк (рассчитать среднее) и по результатам только делать вывод о включении / не включении текущей строки в выборку. И подобных критериев десятки рассчитывающих разные значения. CR>Поэтому я боюсь, что скорость работы будет, мягко говоря, очень низкой, если решать задачу "в лоб".
Тут я хорошо посоветовать не могу, может другие что-нибудь напишут.
Я бы разделил БД. Одна для ввода данных. А другая для отчетов.
На другом сервере восстанавливаете БД из резервной копии.
Пишите программу, которая создает в текущей схеме денормализованные данные для ускорения расчетов:
1) создает новые колонки и таблицы с агрегированными данными, индексы. Обновление схемы можно выполнить с помощью FluentMigrator.
2) выполняет возможные предварительные расчеты для предикатов.
Целевая программа будет крутиться в БД для отчетов.
Если все агрегированные значения посчитать заранее — может быть будет работать достаточно быстро.
Здравствуйте, CyberRussia, Вы писали:
CR>Как все это делать?
Если в запросах будут использоваться много критериев и они часто будут разные, то индексов на всех не напасешься и часто будет идти полное (или частичное) сканирование таблиц. Для SQL-баз поможет только увеличение RAM. Можно также рассчитывать некоторые агрегации заранее и сохранять в БД для последующих запросов.
Если скорости недостаточно, то возможно подойдут аналитические колоночные БД, например clickhouse.
Из плюсов — быстрая работа и поддержка SQL для аналитиков.
Из минусов:
— придется строить процессы периодической выгрузки данных из MSSQL в другую БД
— возможно реализовывать другую схему данных, более подходящую для запросов
Здравствуйте, CyberRussia, Вы писали:
CR>Вопрос — как создаются запросы динамически в себя включающие десятки критериев (от нуля до нескольких десятков), при этом отдельный критерий это не просто выборка значения поля, а расчетное значение учитывающее другие строки (записи) в БД.
Т.е. ты хочешь написать генератор произвольных запросов, где критерии и фильтры будут задаваться чайником кликающим мышкой без знания sql?
CR>Может и стоит, если бы я знал, что это такое.
OLAP — Online analytical processing — если в двух словах, еще одна специальная "база," куда ты загрузишь данные из основной БД, а она пред-расчитает тебе различные агрегаты по основным и расчетным полям.
Пользователь может "крутить", slice'ить и dice'ить, сворачивать и разворачивать эти данные по различным измерения (дата, компания, менеджер, и т.п.) или через спец интерфейс или в том же Excel'е.
Здравствуйте, B7_Ruslan, Вы писали: B_R>К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения.
Вот с чего это вдруг?
Всё, что нас не убивает, ещё горько об этом пожалеет.
