CODEWITHSUNDEEP
sqlsql-serversql-server-2005optimizationcaching
Herb Caudill
Herb Caudill

Reputation: 49952

SQL caching strategy

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:

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?

Upvotes: 1

Views: 758

Answers (3)

Herb Caudill
Herb Caudill

Reputation: 49952

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.

Upvotes: 0

Sylvia
Sylvia

Reputation: 2616

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294237

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?

Upvotes: 1

Related Questions