Debunking Kimball Effective Dates part 2 – Windowing Functions

jamiet — Wed, 13 Jul 2011 17:31:00 GMT

Reigniting the [SCDEndDate] debate

In November 2009 I wrote a blog post entitled Debunking Kimball Effective Dates where I stated that I was against the Kimball-recommended practice of maintaining a StartDate & EndDate value for every record in a type 2 slowly changing dimension (SCD) table (i.e. the values highlighted in blue in the following image):

The post was deliberately inflammatory so as to provoke a debate and it certainly got the desired response as it received 47 comments with a roughly 50-50 split between folks agreeing/disagreeing with me (which was especially interesting given I discussed the same technique back in 2005 but without the inflammatory tone and then received only 6 comments). I really don’t mind if people agree or not, it certainly made for a good debate in those 47 comments.

The crux of the post was thus: the column [SCDEndDate] is unnecessary because it can be derived from [SCDStartDate] of the preceding record. Eliminating that column is therefore beneficial because it can ease the pain of managing those values in an ETL process. Here’s some code that creates the table (without [SCDEndDate]), populates it, then derives [SCDEndDate] in a query:

           DROP TABLE [Customer];  --drop if exists      
           CREATE TABLE [Customer] (      
              [Id]                INT             PRIMARY KEY   IDENTITY(1,1)      
           ,  [NId]               NVARCHAR(50)      
           ,  [Name]              NVARCHAR(50)      
           ,  [Home]              NVARCHAR(50)      
           ,  [MaritalStatus]     CHAR(1)      
           ,  [Descendants]       INT      
           ,  [SCDStartDate]      DATE   --Note only [SCDStartDate], no [SCDEndDate]      
           );      
           CREATE UNIQUE INDEX Cust_idx ON [Customer]([NId],[SCDStartDate]) INCLUDE ([Id],[Name],[Home],[MaritalStatus],[Descendants]);      
           INSERT [Customer] ([NId],[Name],[Home],[MaritalStatus],[Descendants],[SCDStartDate])      
           VALUES ('Cust001','Henry','London','S',0,CONVERT(DATETIME,'20050324'))      
           ,      ('Cust001','Henry','London','M',0,CONVERT(DATETIME,'20070726'))      
           ,      ('Cust002','Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))      
           ,      ('Cust001','Henry','London','M',1,CONVERT(DATETIME,'20091127'));      
           SELECT c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate]      
           ,      COALESCE(MIN(c2.[ScdStartDate]),CONVERT(DATE,'99991231')) AS [SCDEndDate]      
           FROM   [Customer] c      
           LEFT   OUTER JOIN [Customer] c2       ON     c.[Nid] = c2.[Nid]      
           AND        c.[SCDStartDate] < c2.[SCDStartDate]      
           GROUP  BY c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate];

Go and run it now if you’re not au fait with what we’re talking about here! Here is the resultset:

Notice how the [SCDEndDate] for all records pertaining to “Henry” equal the [SCDStartDate] of the following record.

A new way in Denali

Amongst those 47 comments was the following:

Wow, that enormous group by does not look very appealing. Not only is there another layer of abstraction, but when columns get added I have to update a view, and the group by is gonna make it crawl.
-JaggedEdge

That’s a fair point. Not only is there a GROUP BY but if we look at the execution plan we can see that there are two index scans also:

We’re only accessing one table but still get two scans? Yeah, that doesn’t feel good does it? Happily a new feature is coming to SQL Server Denali that makes this easier and is included in yesterday’s CTP3 release; that feature is windowing functions. Below is the same query rewritten to use the new LEAD windowing function:

           SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
                   ISNULL( LEAD([SCDStartDate]) OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC),      
                           CAST('20501231' AS DATETIME)      
                       ) AS [SCDEndDate]      
           FROM    [Customer] c

and here is the resultset and execution plan:

We have the same results (just in a different order) with a different execution plan. There are more physical operations going on here but at least we have eliminated one of those scans. Having said that we have introduced a key lookup operator instead which is easily eliminated by including some columns in our index:

           CREATE UNIQUE INDEX Cust_idx ON [Customer]([NId],[SCDStartDate]) INCLUDE ([Id],[Name],[Home],[MaritalStatus],[Descendants]);

Run it again and our new execution plan is:

The key lookup operator has been eliminated and we have a nice method of deriving [SCDEndDate] without resorting to JOINs and multiple index scans.

Perf testing

Ok, we have eliminated that nasty looking additional Index Scan and Nested Loop Join but the real question is whether this has proved beneficial or not - for that we need a suitably large dataset with which to test. Here’s some code that inserts 140000 rows into our [Customer] table , each time modifying the [NId] column slightly. Its a crude dataset but for our purposes, it will suffice:

       DECLARE @i INT = 0      
       WHILE @i < 35000      
       BEGIN      
                   INSERT [Customer] ([NId],[Name],[Home],[MaritalStatus],[Descendants],[SCDStartDate])      
                   VALUES ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','S',0,CONVERT(DATETIME,'20050324'))      
                   ,      ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','M',0,CONVERT(DATETIME,'20070726'))      
                   ,      ('Cust002' + CAST(@i AS VARCHAR(6)),'Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))      
                   ,      ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','M',1,CONVERT(DATETIME,'20091127'));      
                   SET @i += 1;      
       END

I ran our two queries with STATISTICS IO ON & STATISTICS TIME ON, here are the results:

Method	Scan Count	Logical Reads	CPU time	Elapsed time
JOIN with GROUP BY	18	2902	1637	8232
LEAD	1	2129	718	1853

There are a few stats there but really the most important one is Elapsed time; as you can see the new LEAD method was orders of magnitude faster, it took less than a quarter as long as the old method that I outlined back in the previous blog post.

Conclusion

I still maintain that even pre-Denali you are better off not storing an [SCDEndDate] but now in Denali we have even less reason to do so because the new windowing functions are such a massive boost to performance in these sorts of scenarios. Get to know the new windowing functions well, something tells me you’ll be glad that you did!

@jamiet

Update

After reading Jeremiah Peschka’s post Leaving the Windows Open earlier today I realised that I could make the query containing the LEAD() function a little bit neater. Previously I was using ISNULL() to compensate for there being no match from LEAD like so:

       SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
               ISNULL( LEAD([SCDStartDate]) OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC),      
                       CAST('20501231' AS DATETIME)      
                   ) AS [SCDEndDate]      
       FROM    [Customer] c

Here’s the execution plan again for easy comparison (its different to the one above because this was for a much larger dataset):

Jeremiah’s post taught me that you don’t need ISNULL because LEAD has a similar ability built into it hence I can write the query a little more succinctly like so:

       SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
               LEAD([SCDStartDate], 1, CAST('20501231' AS DATETIME))      
                           OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC) AS [SCDEndDate]      
       FROM    [Customer] c

and the query plan: