Для чего надо.
Кэширование часто используемых нестатических данных, для генерации которых требуется некое неудовлетворительное время, например - ответ от некоего сервиса в сети. Родилось ввиду того, что очень удобно использовать 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.CacheGet(@__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.CacheAdd(@__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 не рекомендует. Аккуратнее с памятью. Сколь-нибудь долговременное хранение данных ни разу не гарантируется.