Creating Hot Swap Tables to Hide Complex Logic

Highly concurrent systems that feed off normalized data stores typically require a middle layer of logic to serve the front-end needs. More often than not, this middle layer of logic is stored in the same stored procedures that the web layer accesses. While sometimes this may be the right place for simple logic, for more complex calculations and joins it is simply not efficient. The answer in these cases is to create a new meta layer of data that pre-joins data and rolls up necessary aggregations.

To paint a better picture – in an ideal database each procedure that feeds the front-end would house a simple select statement from a single table. We know in real life this is not always possible, however we should think in these terms with every web proc we write. The reason is simple – complex logic is both IO and CPU intensive. We have no control over the web traffic, but we do have control over what logic we use to serve the web. Often times it is better to run jobs in the background that perform complicated logic on behalf of the web procs and dump the results into static tables.

This methodology basically creates a denormalized meta layer of data on top of the normalized data. The argument against this is that the data will not truly be real-time. However you need to ask yourself what’s more important, “real-time” data that is 5-10 times slower or preaggregated data that is potentially seconds old?

This leads us to the technique of Creating a Hot Swap table. You can use the AdventureWorks database for this example. Let’s start:

[cc lang=”sql”]

/*******************************************************************
* logic for rollup table
*******************************************************************/
SELECT
product_id = p.ProductID
,product_name = p.Name
,standard_cost = p.StandardCost
,sales_count = sales.sales_count
,rating_count = rating.rating_count
,rating_avg = rating.avg_rating
INTO dbo.product_rollup_load
FROM Production.Product p
CROSS APPLY
(
SELECT
sales_count = count(1)
FROM sales.SalesOrderDetail sod
WHERE sod.productid = p.ProductID
) sales
OUTER APPLY
(
SELECT
avg_rating = ISNULL(AVG(pr.Rating),0)
,rating_count = COUNT(1)
FROM Production.ProductReview pr
WHERE pr.ProductID = p.ProductID
) rating

/*******************************************************************
* apply indexes to the rollup table
*******************************************************************/
CREATE INDEX IX_product_rollup_1 ON product_rollup_load (product_id)

/*******************************************************************
* rename
*******************************************************************/
BEGIN TRANSACTION
EXEC sp_rename ‘product_rollup’, ‘product_rollup_temp’
EXEC sp_rename ‘product_rollup_load’, ‘product_rollup’
DROP TABLE dbo.product_rollup_temp
COMMIT TRANSACTION
[/cc]

Here we see the complex logic is being loaded into a permanent temporary table (not a mistake) “product_rollup_load”. This is not the table the web procs will query, it’s just used to house the data prior to creating our necessary indexes. We create our indexes then within a transaction rename the table the front-end procedures use to an old name, then hot swap the new table to the actual production table. Many Props go out to Tim Collins for turning me on to this technique.

Now, instead of the web procs needing complex logic, a simple SELECT will work just fine. Make sure to test this in your environment. I have seen this used in highly concurrent systems and it works well. It should be noted the other option is to use partitioned tables instead of the rename trick, however we have not run into enough problems using this technique to force that discovery.