(SQL Server) Large Persistent Hash Table Stored on Filesystem

Demonstrates how to implement a large, persistent hash table that is stored on the filesystem and allows for quick retrieval using a hash key.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Let's say we would like to implement a persistent hash table with approximately 200,000 entries.
    -- We want something simple and straightforward.
    -- 
    -- The Chilkat Cache class is a solution that might fit.
    -- 
    -- Each hash table entry is a file.  Depending on the number of anticipated hash entries,
    -- the files can be contained in a single directory, or a collection of 256 directories,
    -- or a collection of 256x256 directories.

    -- There are 3 options:
    -- 
    -- Level 0: All cache files are in a single directory (the cache root).
    -- Level 1: Cache files are located in 256 sub-directories numbered 0 .. 255 directly under the cache root.
    -- Level 2: There are two levels of sub-directories under the cache root. 
    --          The 1st level has 256 sub-directories numbered 0 .. 255 directly under the cache root. 
    --          The 2nd level allows for up to 256 sub-directories (0..255) under each level-1 directory. 
    --          Cache files are stored in the leaf directories. 

    -- For this example, given that we anticipate a larger number of hash entries, we choose a "level 2" cache.
    DECLARE @cache int
    -- Use "Chilkat_9_5_0.Cache" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cache', @cache OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- We can also spread the cache among several root directories, but for this example we'll only use one root directory.
    -- Call AddRoot once for each root directory.
    EXEC sp_OAMethod @cache, 'AddRoot', NULL, 'c:/temp/myCache'
    EXEC sp_OASetProperty @cache, 'Level', 2

    -- Add some key/values to the persisted hash table (i.e. the cache).
    -- The eTag is optional metadata.
    DECLARE @key nvarchar(4000)
    SELECT @key = 'apple'
    DECLARE @eTag nvarchar(4000)
    SELECT @eTag = ''
    DECLARE @itemValue nvarchar(4000)
    SELECT @itemValue = 'macos'
    DECLARE @success int
    EXEC sp_OAMethod @cache, 'SaveTextNoExpire', @success OUT, @key, @eTag, @itemValue

    -- Add more items..
    EXEC sp_OAMethod @cache, 'SaveTextNoExpire', @success OUT, 'microsoft', '', 'windows'
    EXEC sp_OAMethod @cache, 'SaveTextNoExpire', @success OUT, 'google', '', 'android'

    -- Lookup items:
    SELECT @key = 'microsoft'
    EXEC sp_OAMethod @cache, 'FetchText', @itemValue OUT, @key


    PRINT @key + ': ' + @itemValue

    SELECT @key = 'apple'
    EXEC sp_OAMethod @cache, 'FetchText', @itemValue OUT, @key


    PRINT @key + ': ' + @itemValue

    EXEC @hr = sp_OADestroy @cache


END
GO