Использование разреженных столбцов для сокращения повторяющихся данных в файлах журналов

Question

Я создаю приложение, которое записывает показатели производительности автомобилей в таблицу, состоящую из 100 столбцов X 500000 строк на сеанс. Некоторые из столбцов очень повторяющиеся (температура охлаждающей жидкости в градусах Фаренгейта). Другие колонки постоянно меняются (опережение зажигания, давление в коллекторе, соотношение воздух-топливо).

Колоночные магазины и сжатие страниц исключены, поскольку проект нацелен на аудиторию с открытым исходным кодом. Он должен поддерживать MS SQL Express Edition или другую бесплатную платформу базы данных, которая хорошо подходит для таблиц больших размеров.

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

Однако это создает препятствие при выборе дискретных строк, потому что некоторые столбцы будут нулевыми на 99%. Поэтому необходимо создать представление (или вычисляемый столбец?), Которое выберет последнюю строку, содержащую значение в этом поле. Мой подход заключается в использовании подзапроса для каждого разреженного столбца. Это кажется крайне не элегантным. Есть ли более эффективный подход, который я должен рассмотреть?

SELECT ISNULL(
           val1,
           (
               SELECT TOP 1 val1
               FROM   [values] subv
               WHERE  subv.id <= v.id
                      AND subv.val1 IS NOT NULL
               ORDER BY
                      subv.id DESC
           )
       ) AS val1,
       ISNULL(
           val2,
           (
               SELECT TOP 1 val2
               FROM   [values] subv
               WHERE  subv.id <= v.id
                      AND subv.val2 IS NOT NULL
               ORDER BY
                      subv.id DESC
           )
       ) AS val2,
       ISNULL(
           val3,
           (
               SELECT TOP 1 val3
               FROM   [values] subv
               WHERE  subv.id <= v.id
                      AND subv.val3 IS NOT NULL
               ORDER BY
                      subv.id DESC
           )
       ) AS val3
FROM   [values] v

Answer 1

Может быть, лучше вместо этого создать новую таблицу, которая содержит только 3 столбца, например MetricType, Metricvalue, MeasurementTime, Вставляйте новые значения измерений только в том случае, если значение изменилось для определенной метрики.

Answer 2

Я придумал частичное решение.

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

Вместо этого я решил пойти с SQL-CLR. Я написал класс.NET на C#, чтобы сохранить значения между вызовами, и сопоставил это со скалярной функцией SQL. Код не подписан, поэтому для запуска:

1. База данных должна быть помечена как доверенная
2. Сборка CLR должна быть помечена как UNSAFE, чтобы разрешить статические поля
3. Также стоит отметить, что это не многопоточное приложение базы данных и не протестировано или не должно работать как таковое
4. Также следует отметить, что фатальный недостаток этого решения заключается в том, что оно работает только для последовательных упорядоченных выборок, так что я думаю, что это не слишком большое решение.

Вот код deploy.sql (моя сборка называется SqlClr):

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

USE Test

ALTER DATABASE Test SET TRUSTWORTHY ON
GO

IF OBJECT_ID ('cached_value') is not null
BEGIN

    DROP FUNCTION cached_value
END
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SqlClr') 
BEGIN
    DROP ASSEMBLY SqlClr
END
GO

CREATE ASSEMBLY SqlClr 
FROM 'C:\SqlClr\bin\Debug\SqlClr.dll'
WITH PERMISSION_SET = UNSAFE 
GO 

CREATE FUNCTION cached_value(@rowid bigint, @col int, @current_value bigint) 
RETURNS bigint
AS EXTERNAL NAME SqlClr.[SqlClr.StoredProcedures].[cached_value]

Вот код C#.NET:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
using System.Data.SqlClient;

namespace SqlClr
{
    public class StoredProcedures
    {
        public static long last_rowid;
        public static Dictionary<int, long> values = new Dictionary<int, long>();

        [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = false)]
        public static Nullable<long> cached_value(long rowid, int col, Nullable<long> current_value)
        {
            if (rowid < last_rowid)
            {
                values.Clear();
            }

            last_rowid = rowid;

            if (current_value.HasValue)
            {
                values[col] = current_value.Value;
                return current_value.Value;
            }

            if (values.ContainsKey(col))
                return values[col];
            else
                return null;
        }
    }
}

Обратите внимание, что функция.NET должна быть украшена IsDeterministic = false

Наконец, вот пример вызова с использованием функции с 25 столбцами:

SELECT 
dbo.cached_value(id, 1, val1) AS val1,
dbo.cached_value(id, 2, val2) AS val2,
......
dbo.cached_value(id, 25, val25) AS val25
FROM LotsOfValues 

25 столбцов х 500000 строк, заполненных случайным образом 64-разрядными целыми числами и нулями, select * from LotsOfValues занимает 7 секунд, чтобы закончить. С использованием dbo.cached_value(id, n, valn) вызов для всех 25 столбцов, тот же запрос занимает 19 секунд. Я могу обойтись минимум с 50 столбцами, 25 из которых будут редкими, но я думаю, что это увеличит мой потолок до 100. Это приемлемое снижение производительности для приложения, и цель экономии места была достигнута.