sql - 在 SQL Server 中缓存连接表

Question

我的网站有一个运行非常缓慢的搜索程序。使它变慢的一件事是它必须执行的 8 个表连接(它还有一个关于 ~6 个搜索参数的 WHERE 子句)。我尝试使用各种方法(例如添加索引)使查询更快，但这些都没有帮助。

我的一个想法是缓存 8 表连接的结果。我可以创建一个连接的临时表，并让搜索过程查询这个表。我可以每 10 分钟左右更新一次表格。

使用伪代码，我会将程序更改为如下所示:

IF CachedTable is NULL or CachedTable is older than 10 minutes
    DROP TABLE CachedTable
    CREATE TABLE CachedTable as (select * from .....)
ENDIF

Select * from CachedTable Where Name = @SearchName
                            AND EmailAddress = @SearchEmailAddress

这是可行的策略吗？我真的不知道我需要什么语法来完成这个，或者如果两个查询同时发生，我需要锁定什么来阻止事情发生。

此外，每次创建一个新的CachedTable 可能需要相当长的时间，所以我想到在计算机图形中尝试类似双缓冲的方法:

IF CachedTabled is NULL
    CREATE TABLE CachedTable as (select * from ...)
ELSE IF CachedTable is older than 10 minutes
    -- Somehow do this asynchronously, so that the next time a search comes
    -- through the new table is used?
    ASYNCHRONOUS (
        CREATE TABLE BufferedCachedTable as (select * from ...)
        DROP TABLE CachedTable
        RENAME TABLE BufferedCachedTable as CachedTable
    )

Select * from CachedTable Where Name = @SearchName
                            AND EmailAddress = @SearchEmailAddress

这有什么意义吗？如果是这样，我将如何实现它？如果没有，我应该怎么做？我尝试使用索引 View ，但这导致了奇怪的错误，所以我想要这样的东西，我可以更好地控制它(此外，我将来可能会分拆到不同的服务器上。)

另外，对于这样创建的表，索引等怎么办？

从问题中可以明显看出这一点，但我对 SQL 或我可用的选项了解不多。

Answer 1

您可以使用多个模式(您应该始终指定模式!)并像我在 this question 中演示的那样玩 switch-a-roo .基本上您需要两个额外的模式(一个用于临时保存表的副本，一个用于保存缓存的副本)。

CREATE SCHEMA cache AUTHORIZATION dbo;
CREATE SCHEMA hold  AUTHORIZATION dbo;

现在，在缓存模式中创建表的模拟:

SELECT * INTO cache.CachedTable FROM dbo.CachedTable WHERE 1 = 0;
-- then create any indexes etc.

现在到了刷新数据的时候了:

-- step 1:
TRUNCATE TABLE cache.CachedTable;
-- (if you need to maintain FKs you may need to delete)
INSERT INTO cache.CachedTable SELECT ...

-- step 2:
-- this transaction will be almost instantaneous, 
-- since it is a metadata operation only: 

BEGIN TRANSACTION;
  ALTER SCHEMA hold  TRANSFER dbo.Cachedtable;
  ALTER SCHEMA dbo   TRANSFER cache.CachedTable;
  ALTER SCHEMA cache TRANSFER hold.CachedTable;
COMMIT TRANSACTION;