Добрый день!

Помогите, мне, пожалуйста, разобраться как работает связка MSExpress на сервере + Access на десктопах

Я написал примитивный инструмент для анализа данных финансовых отчетностей (база MS SQL Express --> агрегация статистики в показатели в Access --> постобработка в Excel). Хочу как-нибудь сделать так, чтобы инструментом могли пользоваться и мои коллеги. Пытаюсь понять, как локально работающую связку превратить в серверную, но знаний не хватает.

По идее, можно было бы установить БД на сервак, а к ней обращаться с локальных копий Аccess. И обновлять их просто выкладыванием новой версии файла . Но что делать, если пользователь хочет добавить в него свои запросы, и они сотрутся с новой версией файла? В общем, не понятно, как грамотно разбить всё на локальную часть и на серверную

Вопросы:

1) Как вообще в таких случаях поступают во взрослых проектах? Как легко обновлять часть сохраненных запросов в Access (ну или в MS SQL, в конце концов можно и перенести их туда) у пользователей, не стирая собственные запросы пользователя?

2) Как вообще будут работать вычисления, когда в Access файлы таблиц находятся не локально, а на сервере? Access будет гонять при каждом запросе на свою сторону гигабайты данных, чтобы потом посчитать на их основе запросы? Или же он будет передавать свои запросы SQL SERVER, а тот будет их вычислять на своей стороне?

Продолжение в первом комментарии (не влазит в пост).

3) Если запросы обрабатывать на сервере, а один запрос занимает сервер на час, что произойдет, если несколько человек сразу обратятся к серверу?
4) Влияет ли на скорость работы сервера наличие ssd при БД в виде длинной таблицы в 15 млн строк и запросах типа «отфильтровать значения из этой таблицы, потом суммировать», или все утыкается в процессор и скорость сети?
5) Есть ли какие-то способы управлять тем, что будет вычислено на стороне «локального» клиента, а что на стороне сервера в самом запросе?

Подробнее об инструменте:

Есть локально работающая связка SQLEXPRESS (база данных: длинная таблица в 15 млн. строк, в каждой примерно 20 значений) --> Access (запросы, фильтрующие данные по нужным счетам\датам\фирмам, затем суммирующие отфильтрованное и экспортирующие результат в Эксель) --> Excel, который сводит результаты работы Access в удобные таблички.

Архитектура сложилась стихийно. Начал с Access, потом уперся в ограничение размера БД и потому вывел БД в SQLEXPRESS. Запросы из Access в SQL не переносил, так как не знаю отличий в синтаксисе и как делать макросы в MS SQL. В общем, не переносил, чтобы не тратить время на адаптацию. Типичный прогон запроса к БД занимает десктоп с iCore3 первого поколения на час, на выходе запроса 100-50 000 строк.

Почему не оставить всё локально:

Можно было бы установить локально копии инструмента на персональные компы сотрудников, но а) раз в месяц надо добавлять новые данные к БД б) переодически надо обновлять код запросов (правила отчетности меняются).

чччД , спасибо! Не знал, что так можно делать. Буду разбираться.
SERG1257 спасибо за подробный ответ! Бюджета к сожалению фактически нет - Академия наук, поэтому только Express и вряд ли получится привлечь стороннего программиста. Просто хочу, чтобы то, что я сделал не лежало без дела. Реально поначалу будет кто-нибудь гонять запросы раз в несколько недель, но все таки может быть что-то срочное, когда сразу несколько человек обращаются с запросами. Потом если войдут во вкус, можно думать о том как сделать как следует, а пока - пусть это будет сколько-нибудь минимально удобно.

SERG1257 , 982183 Конечно, было бы здорово, если бы участники форума посоветовали мне как переделать запрос.

Злой Бобр , спасибо за пояснение про железо. Да, Вы правы, Access это исторический артефакт.

А вот плохими словами Вы зря бросаетесь, необоснованно :) Логика там такая - выполняется штук 30 запросов, отличающихся только набором счетов в формулах. Каждый такой запрос выдает длинную "колбасу" со значениями показателя на первое число месяца. По сути - временной ряд данных. И все 30 рядов надо собрать в единую табличку с единым значением на дату. Можно начать делать это всякими JOIN, но, насколько я помню, это выходило тормознуто и сложнообновляемо по коду. Вместо этого я эти 30 запросов объединяю в длинную "колбасу", экспортирую её в excel, а там уже не проблема все переделать в удобные таблички. Может можно было как-то проще, но это работает удобно, и этого пока достаточно. Ну, а ряды в табличках уже анализируются всякими графиками\эконометрикой\методом внимательного вглядывания и т.д. Значения-то в базе не одного показателя, а разных, просто в одной форме. Ну а когда надо посчитать показатели сразу для нескольких организаций - колбаса разрастается до 50 000 строк.

Итак,

База. Две одинаковых таблички с кучей строк. Вот здесь поля. Отличаются тем, что в одной табличке данные за старые даты, в другой - за новые. Вызвано тем, что в какой-то момент формулы сильно поменялись, и чтобы на входе в запрос не делать фильтрацию по датам до и после вызываются два подзапроса - по старым данным со старой формулой и по новым данным с новой. База обновляется раз в месяц, просто снизу к табличке дописывается ещё 30 000 строк. Здесь проблем быть не должно.

Запрос фактически один, но его сотня вариаций с разными номерами счетов в формуле.

Суть запроса:

SELECT Номер фирмы, сумма(счета, которые надо сложить) - сумма(счета, которые надо вычесть) + сумма (счета, которые надо прибавить только когда их разница больше нуля), Дата GROUP BY Номер фирмы, дата

Как он выглядит на уровне архитектуры и в реальном виде вот тут
https://www.dropbox.com/s/trwd0dlt6l8omce/Упрощенный пример.docx?dl=0 . Вроде бы получилось написать всё так, что разобраться в нём будет можно за пару минут. Буду благодарен, если поможете советом.

Shakusa,

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

982183 , SERG1257 , Гигабайт Мегабайтович Килобайтов , спасибо большое за советы!

982183 , SERG1257 Спасибо за идею про материализацию. Я раньше отказался от неё, так как в Access было бы очень неудобно обновлять что-либо при такой конструкции и забыл про неё. А теперь, как тут выше сказали, если я верно понял, получается что в коде запросов можно использовать переменные, значит я смогу автоматизировать процесс изменения кода запросов\вычисления новых данных.

Действительно, это снимет огромный кусок вычислительной нагрузки.

Все равно останутся запросы, которые будут требовать полных вычислений - часть формул (комбинаций счетов, подставляемых в приведенный мой пример) неизвестна заранее. Т.е. под конкретную задачу могут потребоваться дополнительные показатели, о которых я не знаю заранее. Но всё равно, большая часть загрузки предложенным способом и правда снимется.

SERG1257 , спасибо за отсылку на columnstore, не знал про них

982183 Access, к сожалению, нет смысла высылать - там полная каша, да ещё и накручено всякого на основую конструкцию. Писал когда-то с нулевым уровнем программирования. То, что по ссылке в десять раз понятнее.

Смысл такой - у каждой из фирм (кредитных организаций) есть порядка 1000 счетов. Каждый счет отвечает за свой вид деятельности и несёт отдельный смысл. По каждому из них, не вдаваясь в бухгалтерские тонкости, есть данные сколько средств в начале месяца, сколько через счет прошло средств за месяц, с разбивкой на рубли и валюту.

Для анализа надо средства на этих счетах суммировать. Ну, например, условно, есть счет «вклады иностранцев сроком до года» и «вклады сроком свыше года». А надо узнать сколько всего вкладов от иностранцев. Складываются два эти счета для каждой организации, а потом, если нужно, по всем организациям, чтобы получить суммарный показатель по всем организациям.
Старые данные актуальны, так как анализировать нужно чаще всего как показатель менялся. Берутся ежемесячные данные за десяток лет, и смотрится, что происходило на графике. Вихлял там, стоял на месте, падал, рос. Дальше смотрится новостной фон, какие-то известные факты, эконометрику можно навесить, и так далее. Но начинается всё с отдельного графика. Иногда приходится так разбирать каждый счет, из которого состоит суммарный показатель, чтобы понять почему именно так всё выглядит. Заранее что именно и как считать не предугадаешь, но да, по основным показателем действительно можно сделать как Вы сказали. Я не задумывался, что это довольно просто с синтаксом MS SQL

Гигабайт Мегабайтович Килобайтов , спасибо! Не знал про лицензии.