SQL缓存策略

Question

我正在编写一个交互式联系人搜索页面(当您键入或选择条件时，通过ajax返回联系人)。我希望这一页能得到很好的响应。

有一组复杂的规则来确定给定联系人可以看到哪些联系人记录；这些规则被卷到用户定义的函数DirectoryContactsByContact(@ContactID)中。我已经对这个函数进行了相当大的优化，但是它仍然有点昂贵(执行1-2秒)，因此为了提高性能，我正在考虑如下所示：

当页面加载时，将该用户的cache_DirectoryContactsByContact_1

• Perform DirectoryContactsByContact缓存为SQL表，例如，对缓存的表(每次检查以确保其存在)进行

• 搜索，然后(比如30分钟)关闭缓存

。

如果数据在这段时间内变得陈旧，这是可以的，所以我不关心失效。

临时表不会在请求之间持续，因此似乎需要将缓存表创建为永久表；但是，我需要自己负责清理旧的缓存，乍一看，这看起来并不简单。

Server中是否有任何机制可以使这更容易？对其他方法有什么建议吗？

Answer 1

最后，我创建了一个基本的通用框架，用于将SQL函数或视图的结果缓存到表中。

    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)

例如，如果SourceView是DirectoryContactsByContact(123)，则创建一个名为_c_DirectoryContactsByContact_123的表。

以下是用于CleanupCacheTables的SQL

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

这是粗糙的:没有失效，而且它可能不会扩展到许多并发用户和/或非常大的数据集。然而，在我的例子中，当用户输入或选择搜索标准时，结果几乎是即时的，开销非常小。

Answer 2

当页面加载时，将函数的结果插入永久表(比如SearchResults )如何？此表的字段如下：

• SearchingContactID
• DirectoryContactID
• CreateDate

你会在这张桌子上搜索。然后-每天或任何时候-你将有一个过程，通过这个表，并删除任何有从超过一天回来。

Answer 3

我不想把数据缓存在.NET中，因为(a)有很多数据，(b)搜索涉及到全文索引和联接以及其他一些SQL做得很好的事情。

这是否意味着搜索的数据是“很多”，还是搜索结果是“很多”？DirectoryContactsByContact(@ContactID)的输出有多大？我的假设是，这是一个小的结果集，小到可以在ASP方面使用。如果是这样，那么您应该在ASP中缓存特定@ContactID的搜索结果，并对相同重复的@ContactID重新缓存结果，直到它从缓存中过期为止，然后重新创建它。

我不太喜欢将结果缓存为SQL中的表。这种方法将读转换为写，从而进一步降低了第一个命中的速度。它提供陈旧的数据，需要清理。但最重要的是，根据我的经验，由于数据模型模式设计不当，它总是避免了固有查询的实际问题。

您对DirectoryContactsByContact(@ContactID)响应时间不能进一步缩短有多大的信心？瓶颈在哪里？你是怎么测量的？您是否考虑过可以进行哪些模式更改以更快地服务于此结果？