Блог

Мы используем SQL Server 2008 R2 Enterprise Edition.

Мы измеряем метеорологические данные из того, что мы называем MetMasts. По сути, это мачта с большим количеством оборудования; анемометры (для измерения скорости ветра) в разных положениях на мачте, термометры и давление воздуха. Мы измеряем каждую секунду.

И это занимает слишком много места на диске. Следующее поколение этого оборудования будет генерировать более 10 ГБ в год каждое. И у нас их будет более 1000.

Текущий дизайн таблицы выглядит примерно так:

     CREATE TABLE #MetMast (
    MetMastID INT NOT NULL IDENTITY(1,1), 
    MetMastName NVARCHAR(100), 
    CountryID INT, 
    InstallDate DATE
)
    CREATE TABLE #MetMastData (
        MetMastDataID BIGINT NOT NULL IDENTITY(1,1),
        MetMastID INT NOT NULL,
        MeasuredAt DATETIME2(0) NOT NULL,
        Temperature REAL NULL,
        WindSpeedAt10m REAL NULL, 
        WindSpeedAt30m REAL NULL,
        AirPressure REAL NULL,
        OneHundredMoreColumns VARCHAR(200),
     CONSTRAINT PK_MetMastData PRIMARY KEY CLUSTERED 
    (
        MetMastID ASC,
        MeasuredAt ASC
    ))
    WITH (DATA_COMPRESSION = ROW) 
    -- ON a file group, with table partitioning
    ALTER TABLE #MetMastData WITH NOCHECK ADD CONSTRAINT FK_MetMast_MetMastID FOREIGN KEY (#MetMast) REFERENCES #MetMast(MetMastID)
  

Данные записываются один раз, считываются много-много раз.
Мы используем его в нашем хранилище данных, где типичным вопросом было бы: Подсчитайте, во сколько раз разница в 2 м / с между скоростями ветра 10 м и 30 м при температуре выше 20 градусов на метамачту.

 SELECT MetMastId, COUNT_BIG(*) FROM #metMastData 
WHERE temperature>20 AND ABS(WindSpeedAt10m-WindSpeedAt30m) >2 
GROUP BY MetMastID
  

В будущем будет допущена небольшая потеря данных.

Здесь мы говорим о сжатии данных с потерями. Я знаю, что нам придется определить допустимую погрешность для каждого из полей, например, в 1%, если мы измеряем с точностью 10%.
Это работало для звуковых файлов (MP3 довольно большой), так что это могло бы сработать и для нас.

Но как это делается?
На какой дизайн стола мне следует обратить внимание?
Как мне начать работу со сжатием данных с потерями в таблицах базы данных?

С наилучшими пожеланиями,

Henrik Staun Poulsen

Для каждой из ваших точек данных учитывайте точность, которую вам необходимо сохранить.

REAL занимает четыре байта для каждой строки. Если бы вы могли удалить все десятичные разряды для скорости ветра, вы, вероятно, могли бы использовать tinyint (1 байт, 1-255). Учитывая, что вам, скорее всего, нужна некоторая точность, вы могли бы вместо этого использовать smallint и умножить фактическое значение на 100:

 150,55 m/s = 15055
3,67 m/s = 367
  

Это сэкономило бы вам два байта на строку и сохранило бы некоторую точность, хотя и с потерей в какой-то момент. Поскольку кажется, что у вас будет довольно много таких столбцов, экономия в 2 байта на столбец составит довольно много.

У вас есть 8-байтовый bigint для вашего MetMastDataID. Необходимо ли это? Не будут ли все запрашиваться MetMastID и MeasuredAT? Удаление этого сэкономит вам 8 байт. Однако это приведет к фрагментации, поскольку ваш кластеризованный ключ больше не будет последовательным, поэтому потребуется дефрагментация. Поскольку это звучит как архивная / OLAP-система, это не должно быть большой проблемой.

РЕДАКТИРОВАТЬ: Я только что понял, что вы не кластеризованы в MetMastDataID, поэтому фрагментация с этого момента не изменится. Тогда вопрос в том, используете ли вы когда-нибудь MetMastDataID для чего-либо?

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

Сжатие с потерями основано на физических возможностях человека определять разницу на глаз или слух. Примерами являются сжатие в формате Mp3 или JPEG с потерями. В вашем случае такой вид сжатия с потерями не имеет смысла, потому что вы работаете с цифрами, а не с аудио / видеоданными. Для реализации сжатия без потерь вы можете использовать функцию CLR.Пример здесь:http://www.codeproject.com/KB/database/blob_compress.aspx.