To use or not to use Temporal Tables in SQL Server in your data warehouse

Posted on by Lulzim Bilali

Recently, Hans Michiels wrote a nice article on how to use temporal tables, which are introduced in SQL Server 2016, in a data warehouse environment. Really recommend you to go through it before. In his case temporal tables were performing better than solutions done in a “traditional way”.

Knowing that temporal tables in SQL Server are just normal tables and there is no “magic” going on underneath, I was curious to know why they were performing so much better.

To be consistent with Hans’s article I will use temporal table and Non-temporal table to refer to the 2 PSA tables, but note that both approaches are temporal solutions and handle the same use case.

Here you can download Hans’s scripts used in his article, which are the starting point of this article as well.

Read performance.

The first thing i looked at was the read performance.

Hans’s results on read were:

Read PSA temporal 164
Read PSA Non-temporal 2686

 

When I run the scripts provided by Hans in his article I got similar results, temporal table having a way better performance than Non-temporal table.

So I checked the execution plans to see what is going on.

The execution plan for the temporal table:

Execution plan for temporal table

As shown above SQL Server is doing a cluster index scan in 2 tables, the non history table and the history table, and then does a union on results.

The plan for the Non-temporal table:

Execution plan for non-temporal table

 

In this case SQL Server decides to do an index seek on the NonClustered index and then a lookup to the Clustered index for getting the rest of the columns.

Hmm…, for some reasons in one table SQL Server decides to use only the Clustered index and in the other table decides to use the NonClustered index and a lookup, even though the same indexes are in both tables.

Well lets see how the non-temporal table will perform if we force to use the Clustered index.

Execution plan of non-temporal table when clustered index is forced

In this case the plan looks very similar with the temporal table. And because there is only one table to be scanned, the performance is actually slightly better.

It must be noted that the Non-temporal table is performing better only if we are reading a point in time in history, but if we want only the actual data then temporal table outperforms the Non-temporal table. To get only actual data SQL Server will use only the non-history table which is smaller in size than the Non-temporal table.

So here we have it. If you are interested only in actual data temporal table performs better, but if you are interested in history then non-temporal table will perform a bit better.

Write performance.

Once finding out what is going on with the reads I had a look at the loading part.

Hans’s results on write were:

Synchronize PSA temporal 6159
Synchronize PSA Non-temporal 24590

 

Running the scripts I got similar results in my machine as well.

So I started to look at the stored procedure `[test].[spLoadTest]` which is used to load the data.

The first thing I noticed was that it has 2 insert scripts when writing the data in the non-temporal table, one for the changes and one for the new records.

Script to handling the changes

INSERT INTO [psa].[Customer_History] ()
SELECT …
FROM
 [stg].[Customer] AS stgc
JOIN
 [psa].[Customer_History] psach
ON stgc.CustomerID = psach.CustomerID
WHERE
 psach.RowHash != stgc.RowHash
AND psach.EffectiveEndDts = @Infinity;

Handling new records

INSERT INTO [psa].[Customer_History] ()
SELECT …
FROM
 [stg].[Customer] stgc
WHERE NOT EXISTS
(
SELECT 1 
FROM [psa].[Customer_History] psach
WHERE stgc.CustomerID = psach.CustomerID
);

I combined the 2 scripts in one like:

INSERT INTO [psa].[Customer_History] (…)
SELECT …
FROM
 [stg].[Customer] AS stgc
WHERE NOT EXISTS
(
SELECT 1 
FROM [psa].[Customer_History] psach
WHERE psach.RowHash != stgc.RowHash
AND psach.EffectiveEndDts = @Infinity
);

Basically we do not care if the record is new or changed all we care about is if the RowHash in stage exists or not in the actual data in the DWH table.
If it does then we have the record, if it does not than there is a change or a new records, so we insert.

I also replaced index [IXNC_Customer_History__CustomerID_RowHash] with the one below to better support this operation.

CREATE NONCLUSTERED INDEX [IXNC_Customer_History__CustomerID_RowHash]
ON [psa].[Customer_History] 
([EffectiveEndDts], [RowHash]);

With this change alone I already got better performance than writing on temporal tables.
Another potential performance issue I noticed was the update EffectiveEndDts sctipt.

The script for the update of EffectiveEndDts of the changed records was:

UPDATE
 data_older
SET
 [EffectiveEndDts] = stgc.SessionStartDts
FROM
 [stg].[Customer] AS stgc
JOIN
 [psa].[Customer_History] AS data_changed
ON data_changed.CustomerID = stgc.CustomerID
AND data_changed.[EffectiveEndDts] = @Infinity
AND data_changed.[SessionStartDts] = stgc.SessionStartDts
JOIN
 [psa].[Customer_History] AS data_older
ON data_older.CustomerID = data_changed.CustomerID
AND data_older.[EffectiveEndDts] = @Infinity
AND data_older.[SessionStartDts] < stgc.SessionStartDts;

I changed it to the one below which touches only the non-temporal table once:

WITH data_older AS (
SELECT EffectiveEndDts
, LEAD(SessionStartDts, 1, EffectiveEndDts) OVER (PARTITION BY CustomerID ORDER BY SessionStartDts) AS new_EffectiveEndDts
FROM [psa].[Customer_History]
WHERE EffectiveEndDts = @Infinity
)
UPDATE data_older
SET EffectiveEndDts = new_EffectiveEndDts
WHERE EffectiveEndDts != new_EffectiveEndDts

To better support this operation I replaced index [IXNC_Customer_History__CustomerID_EffectiveStartDts] with the one below:

CREATE NONCLUSTERED INDEX [IXNC_Customer_History__CustomerID_EffectiveStartDts]
ON [psa].[Customer_History] 
([CustomerID], [EffectiveStartDts]);

After the above changes I got the results below:

Synchronize PSA temporal 4871
Synchronize PSA Non-temporal 2616

As seen from the result, the non-temporal table is almost twice as fast as the temporal table.

Note that after changing the indexes there is no need to force the use of the clustered index when reading. SQL Server will pick the right index now.

Summary

This is based on my results from a limited test case and in your setup the results might be different.

When it comes to reading performance it depends on whether you are reading the actual data or history data. The temporal table will outperform the Non-temporal table on actual data, but when getting the history the Non-temporal table has a bit of an edge.

When it comes to writing, the Non-temporal table outperforms the temporal table.

Maybe in future releases this will change but as it stands today, creating your own temporal solution it gives you more power than using the one shipped with SQL Server 2016.

P.S.

The handling of deletes here is done by closing the deleted record and leaving a gap in the continuity of EffectiveStartDts. I’m not a big fan of this solution and will write another blog about it.