sql-server - 在负载下刷新汇总表的最佳方法是什么？

Question

我在 SQL Server 2005 数据库中创建了一个表，并在其中填充了摘要值和计算值。目的是避免每次调用数据库时进行大量连接和分组。我希望该表每小时刷新一次，但我不确定在网站加载时执行此操作的最佳方法。如果我删除每条记录并在一个事务中重新填充表，这会起作用还是会出现死锁和其他潜在问题？

Answer 1

我在一些项目中执行此操作的方法是在不同模式中使用表的两个副本。所以类似:

CREATE SCHEMA fake WITH AUTHORIZATION dbo;
CREATE SCHEMA standby WITH AUTHORIZATION dbo;
GO

CREATE TABLE dbo.mySummary(<...columns...>);

CREATE TABLE fake.mySummary(<...columns...>);
GO

现在创建一个存储过程来截断并重新填充假表，然后在事务中在架构之间移动对象。

CREATE PROCEDURE dbo.SwapInSummary
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE fake.mySummary;

    INSERT fake.mySummary(<...columns...>)
        SELECT <expensive query>;

    BEGIN TRANSACTION;
        ALTER SCHEMA standby TRANSFER dbo.mySummary;
        ALTER SCHEMA dbo     TRANSFER fake.mySummary;
        ALTER SCHEMA fake    TRANSFER standby.mySummary;
    COMMIT TRANSACTION;
END
GO

这可能是您可以让用户等待新数据刷新且不会在读取过程中中断他们的最短时间。 (与 NOLOCK 相关的许多问题使其成为不太理想的替代方案，尽管不可否认，它很容易编码。)为了简洁/清晰，我省略了错误处理等，并且我还应该指出，如果您使用同步数据库的脚本，确保两个表上的约束、索引等名称相同，否则有一半的时间会不同步。在该过程结束时，您可以截断新的 fake.MySummary 表，但如果您有空间，我喜欢将数据留在那里，以便我始终可以与以前的版本进行比较。

在 SQL Server 2005 之前，我在事务中使用 sp_rename 来完成完全相同的事情，但是由于我在工作中执行此操作，所以我很高兴切换到模式，因为当我这样做时，来自的不可抑制警告sp_rename 停止填充我的 SQL Server 代理历史记录日志。