在 SQL Server 中缓存联接表 [英] Caching joined tables in SQL Server

问题描述

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

My website has a search procedure that runs very slowly. One thing that slows it down is the 8 table join it has to do (It also has a WHERE clause on ~6 search parameters). I've tried to make the query faster using various methods such as adding indexes, but these have not helped.

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

One Idea I have is to cache the result of the 8 table join. I could create a temporary table of the join, and make the search procedure query this table. I could update the table every 10 minutes or so.

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

Using pseudo code, I would change my procedure to look like this:

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

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

Is this a working strategy? I don't really know what syntax I would need to pull this off, or what I would need to lock to stop things from breaking if two queries happen at the same time.

另外，每次创建一个新的CachedTable可能需要相当长的时间，所以我想尝试像计算机图形学中的双缓冲:

Also, it might take quite a long time to make a new CachedTable each time, so I thought of trying something like double buffering in computer graphics:

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

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

Does this make any sense? If so, how would I achieve it? If not, what should I do instead? I tried using indexed views, but this resulted in weird errors, so I want something like this that I can have more control over (Also, something I can potentially spin off onto a different server in the future.)

另外，像这样创建的表的索引等怎么样?

Also, what about indexes and so on for tables created like this?

这可能从问题中很明显，但我对 SQL 或我可用的选项知之甚少.

This may be obvious from the question, but I don't know that much about SQL or the options I have available.

推荐答案

你可以使用多个模式(你应该总是指定模式！)并像我在 这个问题.基本上，您需要两个额外的架构(一个用于临时保存表的副本，另一个用于保存缓存副本).

You can use multiple schemas (you should always specify schema!) and play switch-a-roo as I demonstrated in this question. Basically you need two additional schemas (one to hold a copy of the table temporarily, and one to hold the cached copy).

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

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

Now, create a mimic of the table in the cache schema:

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;

这篇关于在 SQL Server 中缓存联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！