In-memory key-value кэш для MS SQL

Для чего надо.

Кэширование часто используемых нестатических данных, для генерации которых требуется некое неудовлетворительное время, например – ответ от некоего сервиса в сети. Родилось ввиду того, что очень удобно использовать single-value функции в запросах, но нет возможности записи из них в таблицу по понятным причинам.

Варианты решения.

• Таблица для хранения этих данных, процедура обновления в джобе по расписанию либо другие извращенные пляски, какие угодно удобные формы использования/извлечения этих данных.
  Плюсы: удобное, быстрое использование/извлечение данных из таблицы.
  Минусы: дополнительные конструкции и неудобства при кэшировании данных
• Таблица для хранения данных, процедура, извлекающая эти данные. Процедура – сама себе велосипед – проверяет наличие нужных данных в таблице, если есть – отдает, нет – обращается к вышеупомянутому <сервису>, получает данные, записывает в таблицу, опять же отдает.
  Плюсы: компактное решение
  Минусы: жутко неудобное получение данных. Как известно, в MS SQL мы не можем сделать «select * from `StoredProcedure`», а только «insert into `Table` exec `StoredProcedure`»
• Собственно, рассматриваемый вариант. Используем статическую хэш-таблицу и функции получения/записи в нее данных в CLR сборке.
  Плюсы: универсальное и довольно быстрое решение для относительно простых случаев.
  Минусы: использование статики в CLR — не рекомендуемая практика.
• И совсем уже страшный и кривой хак с использованием CLR-функции, которая через отдельное соединение будет записывать данные в таблицу.

Пример, для которого было реализовано — имеется некий сервис в сети, который хранит достаточно часто изменяемое (с плавающей периодичностью, обычно около 10-15 сек.) дерево, и по запросу отдающий небыстро генерируемый путь к указанному по ID листу(ex. ID = 2012, PATH = main/testers/outlet).

На самом SQL сервере

CREATE  FUNCTION [dbo].[GetPath]
(
	@ID  INT
)  
RETURNS VARCHAR(512)
AS  
BEGIN 
	DECLARE @__CACHE_ENABLED BIT = 0; -- вкл/выкл кэширование
	DECLARE @__CACHE_SECTION VARCHAR(50) = 'ggpi'; -- ключ «списка» значений
DECLARE @__CACHE_VALUE_OUTDATE_MS INT = 8000; -- время устаревания значения
DECLARE @Path	VARCHAR(512);
IF (@__CACHE_ENABLED = 1)
	BEGIN
		SET @Path = dbo.cache_get(@__CACHE_SECTION, @ID); -- получить значение
		IF (@Path IS NOT NULL) RETURN @Path;  
END;
-- довольно долгий процесс получения значения откуда-то оттуда
SET @Path = ItsALongLongWayToTipperary(@ID);
	IF (@__CACHE_ENABLED = 1)
	BEGIN
		DECLARE @resf BIT;
		SET @resf = dbo.cache_add(@__CACHE_SECTION, @ID, @Path, @__CACHE_VALUE_OUTDATE_MS); -- и записали полученное значение в наш кэш
	END;

RETURN @Path;
END

И пользуем это счастье в частых пересекающихся по данным запросах вроде

SELECT ID, dbo.GetPath(ID) FROM GROUPS WHERE ID IN (…)

Ну и, для представления, кусок простейшей черновой реализации

private static readonly ThreadSafeDictionary<string, ThreadSafeDictionary<string, CacheData>> Cache = new ThreadSafeDictionary<string, ThreadSafeDictionary<string, CacheData>>();

    [Microsoft.SqlServer.Server.SqlFunction]
    public static bool CacheAdd(SqlString section, SqlString key, SqlString value, SqlInt32 availms)
    {
        ThreadSafeDictionary<string, CacheData> ht;
        Cache.TryGetValue(section.Value, out ht);

        if (null == ht)
        {
            ht = new ThreadSafeDictionary<string, CacheData>();
            Cache[section.Value] = ht;
        }

        DateTime dt = DateTime.Now;
        ht[key.Value] = new CacheData(value.Value, dt.AddMilliseconds(availms.Value));
        return true;
    }


    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString CacheGet(SqlString section, SqlString key)
    {
        ThreadSafeDictionary<string, CacheData> ht;
        bool b = Cache.TryGetValue(section.Value, out ht);

        if (!b || null == ht) return SqlString.Null;

        CacheData sdp;
        b = ht.TryGetValue(key.Value, out sdp);

        if (!b) return SqlString.Null;

        if (sdp.OutDate > DateTime.Now)
        {
            return sdp.Value;
        }
        else
        {
            ht.Remove(key.Value);
        }

        return SqlString.Null;
    }

И напоследок —

Использование статической переменной на SQLServer'e требует для сборки Permission Set = Unrestricted

Решение относится, скорее, к разряду «а что если возьмем и извратимся», но показало приличную производительность на нагрузке.

Полный код сборки, если кому интересно, могу выложить.

Дисклаймер: MS не рекомендует. Аккуратнее с памятью. Сколь-нибудь долговременное хранение данных ни разу не гарантируется.

Автор: yar229