代码之家 › 专栏 › 技术社区 › Herb Caudill

SQL缓存策略

caching optimization sql-server-2005 sql-server sql

Herb Caudill · 技术社区 · 15 年前

我正在开发一个交互式联系人搜索页面(当您键入或选择条件时,联系人通过Ajax返回)。我希望这一页的响应性非常好。

有一组复杂的规则来确定给定联系人可以查看哪些联系人记录;这些规则汇总到用户定义的函数中, DirectoryContactsByContact(@ContactID) .我已经对这个函数进行了相当大的优化,但它仍然有点昂贵(执行时间为1-2秒),所以为了提高性能,我考虑了如下问题:

加载页面时,将此用户的目录联系人缓存为SQL表,例如 cache_DirectoryContactsByContact_1
对缓存表执行搜索(每次检查以确保它存在)
过了一会儿(比如说30分钟)把缓存杀死

在这段时间内,如果数据过时了,也没关系,所以我不关心无效性。

临时表不会在请求之间持续,所以看起来我需要创建一个永久表作为缓存表;但是之后我需要自己负责清理旧的缓存,乍一看,这是非常重要的。

SQL Server中是否有任何机制可以简化这一过程?有关于替代方法的建议吗?

3 回复 | 直到 15 年前

Herb Caudill 15 年前

Remus Rusanu 15 年前

DirectoryContactsByContact(@ContactID)

Herb Caudill 15 年前

    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

        CleanupCacheTables()
        CreateCacheTable(SourceView, FieldList)

DirectoryContactsByContact(123) _c_DirectoryContactsByContact_123

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