Как хранить данные?

[Павел]

Назрел такой вот ламерский вопрос:
Мне в MS SQL нужно хранить парамертры поступающие раз в минуту, параметров примерно 500, все имеют некие характеристики (значение, достоверность значения, свойство значения). Архив должен быть кольцевой(при достижении предельной глубины архива новые записи добавляются, а старые удаляются), глубиной примерно 30 дней.
Как такое хранить в БД?
Если кидать всё в кучу, создав таблицу с полями
<дата/время> <шифр параметра> <значение параметра> <достоверность значения> <свойство значения>
то время выборки будет паршивым: 500 параметров х 43200 минут = 21600000!

Как ещё можно организовать хранение таких данных?

[alibek]

А обязательно MySQL?
Тут удобнее был бы просто файл, открытый для прямого доступа (Open ... As Random).

[Andrey Fedorov]

А обязательно MySQL?
Тут удобнее был бы просто файл, открытый для прямого доступа (Open ... As Random).

Данные, наверно, нужно обрабатывать/анализировать/строить графики - тут база удобней будет.

Я бы сделал в таблице. Индексировать ее по времени и шифру параметра - проблем с выборкой не будет...

[Павел]

Гы-ы ... а время ответа при доступе по сетке к прииндексированной таблице в 21600000 записей (выборка за день+"поля" = 1260 записей) кто-нибудь считал?
У меня вышло па-аршиво, что-то около минуты.

Был ваниант, когда данные за минуту клалась в массив, а его целиком пихали в image поле. Как на VB сделать, я не знаю.
Есть более шустрые варианты хранения таких данных?

[alibek]

Если база организована правильно, то тормозов не будет.
Есть база (правда не MySQL, а Oracle), в ней таблицы, по несколько сотен миллионов записей в каждой. Время доступа - 2-3 секунды.

[Andrey Fedorov]

Гы-ы ... а время ответа при доступе по сетке к прииндексированной таблице в 21600000 записей (выборка за день+"поля" = 1260 записей) кто-нибудь считал?
У меня вышло па-аршиво, что-то около минуты.

А поля-то ты хоть индексировал?
Все моментально должно быть, если по уму сделано.
Что в MDB-шке, что на SQL-сервере.

Был ваниант, когда данные за минуту клалась в массив, а его целиком пихали в image поле. Как на VB сделать, я не знаю.

Да просто скидываешь свой массив на диск - в бинарный файл. Только лишнее это - лучше и удобней базы все одно тут не придумаешь.

[alibek]

А если ты хочешь быстро слить _все_ данные, то спешу тебя обрадовать, не получится. При размере записи в 24 байта (8 байт дата и по 4 все остальное) размер выборки будет около 500Мб. Чтобы "слить" ее за пять секунд, нужна пропускная способность 100 Мб/с. С этим и гигобитная сетка не справится.

[Andrey Fedorov]

To alibek

Есть база (правда не MySQL, а Oracle

)

У него база MSSQL - он писал. Но не столь важно сие.
Скорее всего таблица была без индексов.

А если ты хочешь быстро слить _все_ данные,

Он про дневную выборку говорил.

Вообще у него в дневной выборке должно быть где-то 720000 записей (500 параметров * 1440 минут) что тоже немало. Но может для анализа/обработки не стоит дергать ее целиком на клиента, а обрабатывать в хранимке на сервере и возвращать уже результат...

[Andrey Fedorov]

8 байт дата

Кстати, а нафига - когда есть smalldatetime длиной 4 байта и "разрядностью" в минуту?

[Ennor]

Оптимизировать, кстати, ту еще есть что, это верно. Однако насчет индексов я бы еще подумал. Сами смотрите: вставка 500 записей в минуту, это ж ведь надо не только в страницы таблицы запись сделать, еще и в индексные страницы запись пойдет. Далее: 43 200 минут - это ровно 30 суток. Я правильно понял, что ты собираешься ВСЕГДА вытягивать только всю таблицу, и никогда - часть, по каким-либо критериям? Ибо если я угадал, то варианты есть такие:
1. Отказ от индексов вообще, как ни брутально это звучит. В результате выиграешь на скорости инсертов, а при селекте у тебя Query Optimizer все равно будет строить план с тэйбл сканом, так что индексы ничего не дадут.
2. Построить один кавер индекс на все поля сразу (только учти, что у MSSQL2K ограничение на 16 полей в одном индексе). В этом случае может получиться так, что чтение будет идти именно из этого индекса, а не из таблицы. Но, конечно, инсерты у тебя затормозятся вдвое как минимум. Правда, может и вообще ничего не получиться.

Короче: эксперимент проведи. А то так можно до бесконечности гадать, а потом выяснится, что у тебя реальная задача - получение агрегированного отчета за сутки...

[Павел]

А поля-то ты хоть индексировал?
Все моментально должно быть, если по уму сделано.
Что в MDB-шке, что на SQL-сервере.

Естественно индексировал, но вполне вероятно что "не по уму"
Как по уму для такой таблички сделать?

Кстати, а нафига - когда есть smalldatetime длиной 4 байта и "разрядностью" в минуту?

К сожалению отпадает, есть ещё один архив с частотой 30 сек.

А если ты хочешь быстро слить _все_ данные, то спешу тебя обрадовать, не получится.

Про сеть, как про узкое место я и не подумал, спасибо, на будущее учту. В данном случае 1500*25 байт вроде не очень много.

2Andrey Fedorov и Ennor
Самое ужасное в этой ситуации то, что пользователю нужен именно график минутных значений за сутки Т.е. будет выборка примерно 60-1500 записей из 21600000.

[Ennor]

Ну дык это фигня, поверь мне. Я бы сделал так: построил бы индекс по дате, сделал бы его кластерным с филл-фактором, скажем, 80, ну и по ID датчика, конечно - обычный, филл-фактор 25 максимум, а то и вообще 5-10 - иначе слишком часто Page Split на инсертах ловить будешь. Собсно, все. Если есть еще какие-то критерии, по которым выборка производится, то и их тоже проиндексировал бы, но уже как покрывающий индекс, один на всех. Правда, тут надо аккуратно действовать - кавер-индексы довольно опасны в некоторых ситуациях.

[Павел]

Индекс был обычный (не кластерный), уникальный, по возростанию.
Сейчас наваяю тест, оценим скорость.

[Павел]

Тэ-кс, подводим итоги:

Исходные данные:
Количество параметров - 723
архивация - раз в минуту
глубина архива - 30 дней

Варианты хранения
Таблица 1
Описание: все данные пихаются в одну таблицу, таблица имеет поля

* дата-время
* ID параметра
* значение параметра
* достовертость значения
* режимм (опрос, ручной ввод ...)
* признак учёта

Количество записей = 723*30*24*60=31233600 записей
Индексы - как посоветовал Ennor
Результат: запрос 20-и параметров за 1 сутки выполняется 7 сек

Таблица 2
Каждый параметр пихается в свою таблицу, создаётся 723 таблицы, вид таблиц:

* дата-время
* значение параметра
* достовертость значения
* режимм (опрос, ручной ввод ...)
* признак учёта

количество записей в каждой 30*24*60 = 43200 записей
индексы - аналогично
Результат: запрос 20-и параметров за 1 сутки выполняется 0.5-1 сек

Это всё на MS, SQL для Access побольше будет, но второй вариант - выгоднее. Плюс, при первом варианте апдейт или инсёрт одной(!) записи идёт более 5 секунд.

Что скажите?[/i]

[Ennor]

Ммм. Так...
Ни в коем случае не используй второй вариант - он слишком негибок. Представь себе, что потребуется объединять датчики в группы по некоему критерию. И что, будешь писать отдельный селект на каждый случай, или будешь извращаться с Dynamic SQL?

Насчет времени вставки - это индексы мутят, как пить дать (ох, проклятый сушняк... ). Давай прикинем, по какому критерию у тебя чаще всего идет поиск:

1. CheckDate - однозначно. В принципе, я наверное перестарался, сказав, что это должен быть кластерный индекс, но я не вижу больше ни одного кандидата на эту роль. А сортировать тебе надо именно по времени, верно? Кроме того, вставка задним числом у тебя невозможна в силу бизнес-логики. Значит, оставляем его как есть (Primary Key Clustered), только ставим ему Fill Factor = 50. Ну или меньше, вплоть до 10, если поможет, конечно. Уберешь кластерность - появятся доп. расходы на сортировку;

2. ParameterId - говоришь, их у тебя 723... Попробуй выставить обычный индекс, но филл фактор = 5, более того, попробуй также поставить галку PAD Index - эта опция заставляет оставлять свободное место не только в конечных страницах индекса, но и в промежуточных. Не исключено, что это как раз твой случай. Одна только проблема - когда ты дойдешь до исчерпания свободного места с страницах индекса, первый же инсерт у тебя столько займет времени... Страшно подумать. Но это уже вопросы администрирования, и здесь поможет грамотный Maintenance Plan с DBCC DBREINDEX / INDEXDEFRAG...

3. Кавер-индекс на все остальные поля. Филл-фактор... ну, я бы попробовал оставить дефолтный 0 - пусть сиквел сам думает, у него процессоров больше, чем у меня голов . Либо вообще его дропни и посмотри, сильно ли изменится скорость инсертов/селектов.

Вот что меня еще интересует: у тебя приоритет на скорость вставки записей или на скорость получения отчета? Если на первое, то пункт 3 я бы вообще удалил. Если же на второе, то можно немного закрутить фил-факторы в большую сторону. Но это уже детали, конечно...