SQL caching strategy

Question

I'm working on an interactive contact search page (contacts are returned via ajax as you type or select criteria). I want this page to be very responsive.

There is a complex set of rules to determine which contact records a given contact can see; these rules are rolled up into a user-defined function, DirectoryContactsByContact(@ContactID). I've optimized this function considerably but it's still a little expensive (1-2 seconds to execute), so to improve performance I'm thinking about something like this:

• When the page loads, cache DirectoryContactsByContact for this user as a SQL table, e.g. cache_DirectoryContactsByContact_1
• Perform the search against the cached table (checking each time to make sure it exists)
• After a little while (say 30 minutes) kill the cache

It's OK if the data gets stale during this period, so I'm not concerned with invalidation.

Temporary tables don't last between requests, so it seems like I'd need to create the cache table as a permanent table; but then I'd need to be responsible for cleaning up old caches myself, which looks non-trivial at first glance.

Are there any mechanisms within SQL Server that would make this easier? Any advice on alternative approaches?

Answer 1

How about whenever the page loads, inserting the results of your function to a permanent table, say SearchResults. This table would have fields like:

• SearchingContactID
• DirectoryContactID
• CreateDate

You would search against this table. Then - daily or whenever - you would have a process to go through this table and delete whatever is there from more than a day or so back.

Answer 2

I don't want to cache the data in memory in .NET because (a) there's a lot of data, and (b) the search involves full-text indexes and joins and other stuff that SQL does well.

Does that mean that the searched data is 'a lot', or that the search result is a 'a lot'? How big is the output of DirectoryContactsByContact(@ContactID)? My presumption is that is a small result set, small enough to be usefull on the ASP side. If that's true, then you should cache in ASP the search result for a particular @ContactID, and resuse that cached result for the same repeated @ContactID until it expires from cache, then re-create it.

I'm not a big fan of caching results as tables in SQL. This approach turns reads into writes, thus slowing down the first hit even more. It offers stale data, it requires cleanup. But most importantly, from my experience, it always circumvent the real problem of inneficient queries due to improperly designed data model schema.

How confident are you that the DirectoryContactsByContact(@ContactID) response time cannot be further reduced? Where is the bottleneck? How did you measure it? Have you considered what schema changes could be done to serve this result faster?

Answer 3

I've ended up creating a rudimentary general-purpose framework for caching the results of a SQL function or view to a table.

    Public Sub CreateCacheTable(ByVal SourceView As String, ByVal FieldList As String)
        Dim CacheTable As String = GetCacheTableName(SourceView)
        If Not TableExists(CacheTable) Then
            Dim Sql As String = " Select ~FieldList~ Into ~CacheTable~ From ~SourceView~ ". _
                Replace("~CacheTable~", CacheTable). _
                Replace("~FieldList~", FieldList). _
                Replace("~SourceView~", SourceView)
            ExecuteNonQuery(cs, CommandType.Text, Sql)
        End If
    End Sub

    Public Function GetCacheTableName(ByVal SourceView As String)
        Dim Result As String = "_c_~SourceView~". _
            Replace("~SourceView~", SourceView). _
            Replace(".", "_"). _
            Replace(",", "_"). _
            Replace("[", ""). _
            Replace("]", ""). _
            Replace("(", ""). _
            Replace(")", "")
        Return Result
    End Function

    Public Sub CleanupCacheTables()
        ExecuteNonQuery(cs, CommandType.StoredProcedure, "CleanupCacheTables") 
    End Sub

When the page loads I do this:

        CleanupCacheTables()
        CreateCacheTable(SourceView, FieldList)

For example, if SourceView is DirectoryContactsByContact(123) this creates a table named _c_DirectoryContactsByContact_123.

Here's the SQL for CleanupCacheTables:

Create Procedure CleanupCacheTables as
    /* Finds all tables starting with _c_ that were created more than 30 minutes ago and drops them */
    Declare @TableName nvarchar(255)
    Declare CacheTableCursor Cursor for
        Select 
            TableName=name
        From SYS.OBJECTS
        Where Type_Desc = 'USER_TABLE'
        And Left(name,3)=  '_c_'
        And DateDiff(minute, create_date, GetDate())>30
    Open CacheTableCursor
    Fetch Next from CacheTableCursor into @TableName
    While @@FETCH_STATUS = 0 Begin
        Exec ('Drop Table ' + @TableName)
        Fetch Next from CacheTableCursor into @TableName
    End -- While
    Close CacheTableCursor
    Deallocate CacheTableCursor
Go

This is crude: There's no invalidation, and it probably won't scale to a lot of concurrent users and/or very large datasets. Nevertheless, in my case it's resulted in near-instantaneous results as the user types or selects search criteria, with very little overhead.