как правильней сгрупировать(sum) строки перед записью в БД?

[Mugger]

Здравствуйте.

Задача.
Есть данные, примерно 1000 строк из 5 полей в минуту.
Каждые 5 минут нужно часть данных объединять по определённому правилу.
В итоге объединения(групировки) получается 100-200 строк.

Есть сервер sql сервер. На него должны быть записаны итоговые строки.


Как это сделать оптимально?

Сейчас делаю так.
Создаю локальную dbf БД. Туда записываю поступающие данные, затем SQL запросом делаю выборку с группировкой записей.
Потом записываю сгруппированные записи на sql сервер.
Затем пересоздаю dbf фаил.

Работает такая схема очень медленно. Есть ли ещё способы?

[mihali4]

Конечно. Почитайте про сторед-процедуры. Зачем "гонять" данные туда-сюда? Все делается на стороне сервера.
Правда, если ваша "группировка (объединение по определённому правилу)" заключается в вычислении суммарных значений и зачем-то занесения их в базу (явная избыточность), то смысл всей этой катавасии совершенно непонятен.

[Mugger]

Спасибо.

Смысл группировки - суммирование полей записей, которые имеют одинаковые определённые ключевые поля.
пример:

1. 155 8 вася
2. 566 9 петя
3 98 3 вася

В итоге будет две строчки.

1. 253 11 вася
2. 566 9 петя


По расчётам за 5 минут набегает 10000-20000 записей.
которые в результате группировки превращаются в 300-400 записей. Они то нам и нужны для дальнейшей работы. А исходные 20т. записей удаляются.

Гонять все 20000 записей на sql сервер по сети мне кажется не правильным.
Хочу всё локально обработать, на стороне приложения.


Про сторед процедуры почитаю. Но мне кажется что это немножко не то что мне нужно.
Вспомнил про кэширование данных. Только не помню можно ли выполнять SQL запросы внутри кэшируемых данных. Переберать циклом строки будет достаточно медленно...

[mihali4]

Создаю локальную dbf БД. Туда записываю поступающие данные, затем SQL запросом делаю выборку с группировкой записей.
Потом записываю сгруппированные записи на sql сервер.
Затем пересоздаю dbf фаил.

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

[Mugger]

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

С кэшированием пока не разобрался. Неуверен что можно делать выборку внутри кэшированных данных. Хотя вроде должно быть.
(Пока небыло времени полазить по просторам интернета)

Может есть ещё варианты?

[mihali4]

Сообщение от Mugger

все данные на сервер пересылать нет смысла. Надо локально сформировать сумарные данные и потом их уже записывать на сервер.

Так вы все верно описали.
- вы получаете свои очередные 20000 новых замеров;
- упаковываете их по вашему алгоритму;
- и на сервер вы отсылаете ваш апдейт - например:
UPDATE MYTABLE SET kolvo=новое_значение WHERE name='Ваня'

По расчётам за 5 минут набегает 10000-20000 записей.
которые в результате группировки превращаются в 300-400 записей. Они то нам и нужны для дальнейшей работы. А исходные 20т. записей удаляются.
Гонять все 20000 записей на sql сервер по сети мне кажется не правильным.

Так тут и "гонятся" только исправления (или дополнения). Т.е. не 20000, а 300-400...
Или мы опять друг друга не поняли?

[Mugger]

:-) Ну почти.

Вопрос в том, как оптимально локально накопить данные, а потом их упаковать.

Я их записываю в дбф фаил, и затем делаю выборку. Это достаточно медленно.
А в заключение я делаю файловую операцию - пересоздание дбф файла. (Для удаления ненужных данных)
Суммарно это занимает достаточно много времени и ресурсов.
Также мне приходиться использовать дополнительную БД файлового типа.

Вопрос: Можно ли сделать накопление и выборку более оптимально или более "правильно". Например без использования дополнительной БД.

[Timpi]

Сообщение от Mugger

Я их записываю в дбф фаил, и затем делаю выборку. Это достаточно медленно.

Странно, должно всё быстро происходить, если только не индексируешь нужные поля или индексируешь слишком много полей (при том не нужных) или делаешь не оптимальный запрос.

Как я понимаю, у тебя сгрупированные данные записываются в другую таблицу. Если так, то проблем то вообще не должно быть: делаешь выборку по челу с суммированием по нужным полям, записываешь в нужную таблицу с суммированием полученных данных и уже записанных в ней данных, удаляешь из буферной таблицы обработанные записи. Прмерно так:
1. SELECT SUM(buf_tab.field1), SUM(buf_tab.field2), ... FROM buf_tab WHERE buf_tab.key_field = key_value
- у тебя появляется выборка с одной записью
2. UPDATE dest_tab SET
dest_tab.field1 = value1 + (SELECT dest_tab.field1 FROM dest_tab WHERE dest_tab.key_field = key_value),
dest_tab.field2 = value2 + (SELECT dest_tab.field2 FROM dest_tab WHERE dest_tab.key_field = key_value),
...
WHERE dest_tab.key_field = key_value
- value1, value2, ... сумарные значения из шага 1
3. DELETE FROM buf_tab WHERE buf_tab.key_field = key_value
- удаляешь обработанные записи.
Перебирая key_value должно быть достигнуто твоё решение задачи.
Может SQL-запросы можно сделать и пооптимальнее, но с лёту написал такие.

P.S. Вообще всё это похоже на ИБД (иммитация бурной деятельности): я работаю, каждые 5 минут нажимаю на кнопки, удаляю и создаю файлы. По хорошему всё должно обрабатываться на сервере - поступили данные и тут же записались с полной обработкой (сразу прибавились к уже существующим, без промежуточных таблиц). Промежуточные группировки на отдельном сервере имеет смысл делать: (1) если узкий канал, (2) если будет реальная экономия на интернет-трафике, (3) если не стабильная связь с главным сервером, (4) если гл. сервер сильно загружен и не успевает обрабатывать все поступающие запросы. Чтобы не создавать себе лишний геморрой надо поступившие данные обрабатывать сразу, а не записывать в таблицу и потом их обрабатывать, далее каждые 5 минут их сливать на сервер.
Результат: экономится траффик, время, нет геморроя.
Процедуры, хорошая вещь, смотри помогут они тебе или нет: траффик не гоняется туда-сюда всё обрабатывается на одном сервере, в одну процедуру можно записать несколько SQL-запросов, а так же использовать некоторые операторы, которые нельзя использовать в обычном запросе (например циклы и условия), всё работает в одной среде без передачи в другую, т.е. экономится время.

[vovk]

Если я правильно понял задачу,
то почему бы на клиенте не создать несколько переменных в которые суммировать (или производить другие нужные вам действия) полученные данные а потом, через определённый интервал, записывать значения в базу?

[Mugger]

Timpi, нехочу гонять по сети лишний трафик.
Плюс время записи 10000 строк(по одной) по сети, будет скорее всего происходить медленнее чем тоже самое но в локальную базу.

Схема описанная мною выше - работает дня 4-5 - вроде справляется.

Проблема в том что данные валятся мне на порт партиями. И промедление даже на 1 секунду грозит потерей нескольких пакетов которые отвалятся по таймауту.
Поэтому я хотел найти способ делать накопление и группировку данных например в памяти машины, а уже результаты отправлять на сервер.

vovk, думал об этом. скорее тут может идти речь об динамическом массиве объектов. Но я не уверен что ручная обработка этого массива будет быстрее группировки(выборки) локальной БД.


Ещё раз подчеркну основной вопрос:

Как максимально оптимально получить 10000 записей, сгруппировать их до 300, и отправить на сетевой сервер.
Оптимально - минимум времени на складирование записей в процессе получения, и минимум времени на группировку раз в 5 минут.


Канал до сервера 2 мбит/сек…
В рабочие часы забит сильно.