How do I periodically rebuild a reporting table th

问题:

It takes about 5-10 minutes to refresh a prepared reporting table. We want to refresh this table constantly (maybe once every 15 minutes or continuously).

We query this reporting table very frequently (many times per minute) and I can't keep it down for any length of time. It is okay if the data is 15 minutes old.

I can't drop the table and recreate it. I can't delete the table's contents and recreate it.

Is there a technique I should be using, like swapping between two tables (read from one while we build the other) or do I put this 5-10 minute process in a large transaction?

回答1:

Use synonyms?. On creation this points to tableA.

CREATE SYNONYM ReportingTable FOR dbo.tableA;

15 minutes later you create tableB and redefine the synonym

DROP SYNONYM ReportingTable;
CREATE SYNONYM ReportingTable FOR dbo.tableB;

The synonym is merely a pointer to the actual table: this way the handling of the actual table renames etc is simplified and abstracted away and all code/clients would use ReportingTable

Edit, 24 Nov 2011

Synonyms are available in all edition: partition switching is Enterprise/Developer only.

Edit, Feb 2012

You can switch whole tables in standard edition (maybe Express, untested)

ALTER TABLE .. SWITCH ..

This would be more elegant than synonyms if the target table is empty.

Edit, Feb 2012 (2)

Also, you can rotate via schemas as per Caching joined tables in SQL Server

回答2:

Yes, you should swap tables, and if not already done, consider using a different server or other physical partitioning for the reporting table.

The recommended approach for near real-time reporting is to offload reads from the operational system, and separate write activity from read activity in the reporting system.

You have done the first part, at least logically, by having a prepared table. Swapping between a read-only table for users and a separate table for updates eliminates write-read conflicts between transactions. The cost is the cache latency for users, but if required, it should be possible to make steps to minimize the preparation time and and swap the tables more often.

For more information on design choices in real-time reporting, I recommend a well written paper by Wayne Eckerson, Best Practices in Operational BI.

回答3:

Having two tables sounds like the simplest solution.

回答4:

In our project, We used two tables, and Create/Alter View to switch.