在负载下刷新汇总表的最佳方法是什么？ [英] What is the best way to refresh a rollup table under load?

问题描述

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

I created a table in my SQL Server 2005 database and populated it with summary and calculated values. The purpose is to avoid extensive joins and groupings on every call to the database. I would like this table to refresh every hour, but I am not sure the best way to do this while the website is under load. If I delete every record and repopulate the table in one transaction will that do the trick or will there be deadlocks and other trouble lurking?

推荐答案

<在一些项目中，我这样做的方法是在不同的架构中使用表的两个副本。像这样：

The way I have done this in a few projects is to use two copies of the table in different schemas. So something like:

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

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

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

现在创建一个存储过程，该过程将截断并重新填充假表，然后

Now create a stored procedure that truncates and re-populates the fake table, then in a transaction move the objects between schemas.

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表，但是如果有空间，我希望将数据保留在那里，以便始终与以前的版本进行比较。

This is probably about the shortest amount of time you can make users wait for the new data to be refreshed and without disrupting them in the middle of a read. (There are many issues associated with NOLOCK that make it a less desirable alternative, though admittedly, it is easy to code.) For brevity/clarity I've left out error handling etc., and I should also point out that if you use scripts to synchronize your databases, make sure you name constraints, indexes etc. the same on both tables, otherwise you will be out of sync half of the time. At the end of the procedure you can TRUNCATE the new fake.MySummary table, but if you have the space, I like to leave the data there so I can always compare to the previous version.

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

Before SQL Server 2005 I used sp_rename inside the transaction to accomplish exactly the same thing, however since I do this in a job, I was glad about switching to schemas, because when I did, the non-suppress-able warning from sp_rename stopped filling up my SQL Server Agent history logs.

这篇关于在负载下刷新汇总表的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！