如何定期重建访问非常频繁的报表? [英] How do I periodically rebuild a reporting table that is very frequently accessed?

问题描述

刷新准备好的报告表大约需要 5-10 分钟.我们希望不断刷新此表(可能每 15 分钟一次或连续刷新一次).

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).

我们非常频繁地(每分钟多次)查询此报表，我无法将其保留很长时间.如果数据是 15 分钟前的就可以了.

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.

有没有我应该使用的技术，比如在两个表之间交换(在我们构建另一个表时从一个表中读取)，或者我是否将这个 5-10 分钟的过程放在一个大事务中?

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?

推荐答案

使用同义词?.在创建时，它指向 tableA.

Use synonyms?. On creation this points to tableA.

CREATE SYNONYM ReportingTable FOR dbo.tableA;

15 分钟后您创建 tableB 并重新定义同义词

15 minutes later you create tableB and redefine the synonym

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

同义词只是一个指向实际表的指针:通过这种方式，实际表重命名等的处理被简化和抽象掉，所有代码/客户端都将使用 ReportingTable

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

编辑，2011 年 11 月 24 日

Edit, 24 Nov 2011

同义词适用于所有版本:分区切换仅适用于企业/开发者.

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

编辑，2012 年 2 月

Edit, Feb 2012

您可以在标准版本中切换整个表格(可能是 Express，未经测试)

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.

编辑，2012 年 2 月 (2)

Edit, Feb 2012 (2)

此外，您可以按照 缓存 SQL Server 中的连接表

这篇关于如何定期重建访问非常频繁的报表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！