In-memory key-value cache for MS SQL

What is necessary for.

Caching frequently used non-static data, which takes an unsatisfactory time to generate, for example, a response from a certain service on the network. It was born in view of the fact that it is very convenient to use single-value functions in queries, but there is no possibility of writing them into a table for obvious reasons.

Variants of the decision.

• The table for storing this data, the update procedure in the job according to the schedule or other perverted dances, any convenient forms of using / retrieving this data.
  Pros: convenient, quick use / extraction of data from the table.
  Cons: additional design and inconvenience when caching data
• A table for storing data, a procedure that retrieves this data. The procedure - the bike itself - checks for the availability of the necessary data in the table, if there is - gives, no - refers to the above-mentioned <service>, receives data, writes to the table, again gives.
  Pros: compact solution
  Cons: terribly inconvenient data acquisition. As you know, in MS SQL we cannot do “select * from` StoredProcedure` ", but only" insert into `Table` exec` StoredProcedure` "
• Actually, the considered option. We use a static hash table and functions to get / write data to it in the CLR assembly.
  Pros: a universal and fairly quick solution for relatively simple cases.
  Cons: Static usage in the CLR is not a recommended practice.
• And quite terrible and crooked hack using the CLR-function, which through a separate connection will write data to the table.

An example for which it was implemented - there is a certain service on the network that stores quite often variable (with a floating periodicity, usually about 10-15 seconds) tree, and on request gives a non-quickly generated path to the specified by ID sheet (ex. ID = 2012, PATH = main / testers / outlet).
')
On the SQL server itself

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 

And we use this happiness in frequent intersecting queries like

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

Well, for presentation, a piece of the simplest draft implementation

 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; } 

And finally -

Using a static variable on SQLServer requires building Permission Set = Unrestricted to build

The solution is rather a “what if we take and we distort” category, but it showed decent performance on the load.

The full assembly code, if anyone is interested, I can lay out.

Disclaimer: MS does not recommend. Be careful with the memory. Long-term data storage is never guaranteed.