У меня похожая ситуация, когда я загружаю кучу датчиков температуры каждые 10 секунд. Поскольку я использую экспресс-версию MSSQL, я смотрю на максимальный размер базы данных 10 ГБ, поэтому я проявил творческий подход, чтобы продлить ее как можно дольше. Мой макет таблицы почти идентичен вашему в том, что у меня есть 1 метка времени + 30 столбцов значений + еще 30 столбцов флагов.
- Столбцы значений являются числовыми (9,2)
- Столбцы значений помечены как SPARSE, если значение идентично (достаточно) значению перед ним, я сохраняю NULL вместо повторения значения.
- Столбцы флагов являются битовыми и указывают, является ли значение «экстраполированным» или полученным из фактического источника (позже).
У меня также есть еще одна таблица, которая содержит следующую информацию для каждого датчика:
- последний раз датчик обновлялся; таким образом, если появляется новое значение, я могу легко решить, требуется ли для этого только новая вставка в конце таблицы или мне нужно пройти всю логику вставки/обновления значения где-то между существующими числами.
- значение этой последней записи
- чувствительность указанного датчика; таким образом, мне не нужно жестко кодировать его, и я могу установить его для каждого датчика
В любом случае, на данный момент мой поток информации состоит в том, что у меня есть несколько программ, каждая из которых читает данные из разных источников (ардуино, Интернет, ...) и выгружает их в файлы .csv, а затем программа «парсер», которая читает эти файлы в базу данных время от времени. Поскольку я загружаю значения 1 на 1, а не по строкам, это не очень эффективно, но сейчас я делаю около 3500 значений в секунду, поэтому меня это не слишком беспокоит. Я согласен, что это верно только при загрузке значений в историческом порядке и потому, что я использую вспомогательную таблицу.
В настоящее время у меня есть почти 1 год информации, которая соответствует
- 2 209 883 строки
- 5.799.511 значений распределены по 18 датчикам (да, у меня еще есть место для еще 12 без необходимости менять таблицу)
Это означает, что у меня заполнено только 15% полей, или, глядя на это наоборот, когда я заполнил бы каждую запись, а не ставил NULL в случае повторения, у меня было бы почти в 8 раз больше номера там.
Что касается требований к пространству: прошлой ночью я решил перезагрузить все числа «для развлечения», но заметил, что, хотя большинство файлов .csv поступают исторически, они будут отображать диапазон столбцов с января по декабрь, а затем еще пару столбцов с января по Декабрь и т. д. Это привело к значительной фрагментации: фактически 70%! В то время для таблицы требовалось 282 МБ дискового пространства. Затем я перестроил таблицу, снизив фрагментацию до 0%, а зарезервированное пространство уменьшилось до 118 МБ (!).
Для меня этого более чем достаточно
- маловероятно, что в ближайшее время таблица перерастет ограничение в 10 ГБ, особенно если я буду время от времени перестраивать ее (онлайн).
- загрузка данных достаточно быстрая (хотя перезагрузка всего года заняла пару часов)
- отчетность достаточно быстрая (пока еще не пробовал подключать к ней какие-либо «интерактивные» инструменты отчетности, но для некоторых простых графиков в excel работает просто отлично ИМХО).
К вашему сведению: для отчетности я создал довольно простую хранимую процедуру, которая выбирает диапазон от-до для заданного набора столбцов; сбрасывает его во временную таблицу, а затем заполняет пробелы, определяя диапазоны NULL и затем заполняя их значением, которое предшествовало диапазону. Это работает довольно хорошо, хотя выбор «первого» значения иногда занимает некоторое время, так как я не могу предсказать, как далеко назад во времени следует искать последнее значение (иногда его нет). Чтобы обойти это, я добавил еще один процесс, который экстраполирует значения для каждой временной метки «час». Таким образом, отчету никогда не нужно возвращаться назад более чем на 1 час. Столбец флага в таблице показаний указывает, было ли экстраполировано значение записи для данного поля или нет. (примечание: это делает обновление значений в прошлом более проблематичным, но не невозможным)
Надеюсь, это немного поможет вам в ваших начинаниях, удачи!
person
deroby
schedule
31.12.2018
