sql server - What is the best way to refresh a rollup table under load?

Question

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?

Answer 1

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

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.

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.