To use or not to use Temporal Tables in SQL Server in your data warehouse
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.
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:
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:
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.
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.
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.
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.
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.