10

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?

Jason
  • 16,739
  • 23
  • 87
  • 137
  • When you implemented your solution, did you use sp_getapplock? And, if you did, was the sp_getapplock ONLY encasing the DROP and CREATE of your SYNONYM(s)? – mg1075 Jul 31 '11 at 01:17

4 Answers4

14

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • If the synonym is constantly in use, is it possible to recreate it without getting errors? Does this require a retry mechanism? – Chris Simpson Jun 07 '11 at 16:58
  • @Chris Simpson: the synonym isn't in use *per se*. DROP it and calls break: it won't lock or block anything. Wrapping the DROP/CREATE in a transaction should fix this: it's still lighter then messing with tables – gbn Jun 07 '11 at 17:05
  • That's interesting. So the DROP would be allowed because the synonym has already resolved to a real table in a running query. I was curious about wrapping schema changes in transactions and came across your answer to this one: http://stackoverflow.com/questions/4166989/alternate-synonym-in-sql-server-in-one-transaction , would that be beneficial here? – Chris Simpson Jun 07 '11 at 17:12
  • @Chris Simpson: yes, good spot. A transaction lock via sp_getapplock would be the way I'd probably do it. – gbn Jun 07 '11 at 17:20
  • This sounds interesting, given that [partition switching](http://msdn.microsoft.com/en-us/library/ms191160.aspx) is available only in the enterprise edition. One difference is that partition switching takes a schema-modify lock to both tables, which blocks everything. – mika Jun 07 '11 at 17:48
1

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.

mika
  • 6,812
  • 4
  • 35
  • 38
0

Having two tables sounds like the simplest solution.

MRAB
  • 20,356
  • 6
  • 40
  • 33
  • And is there an easy way to swap between them, or to realize which one to use? If I have to alter all my read queries to point to two tables, this seems very extreme. – Jason Jun 07 '11 at 16:21
  • I would rename the tables, inside a transaction. – MRAB Jun 07 '11 at 16:30
  • object names still need to be unique regardless of transaction – gbn Jun 07 '11 at 16:50
0

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

EricZ
  • 6,065
  • 1
  • 30
  • 30