• Les Cardwell (3/15/2010)


    In spite of the criticism, it was still a simple example of minimal denormalization to achieve an end result rather than a full-on explosion of wide rows to reduce the NF to 0 :pinch:

    Interestingly enough, the biggest cost to the initial query, which probably exceeded benefits of denormalization, was using a 'function' in a WHERE predicate...

    WHERE P_MS.DateReceived > getdate() - 365

    ...would have been better expressed declaring a scalar variable:

    DECLARE @selectDate = getdate()-365

    ...

    WHERE P_MS.DateReceived > @selectDate

    ...

    ...which would allow the optimizer to use an index on DateReceived.

    Unfortunately, denormalization for immutable datasets as we've used it in the past just doesn't scale, especially on large datasets...not to mention the escalating complexity (and headaches) it entails. Ironically, not even for data-warehouses that go the same route (MOLAP) vs. a Multi-dimensional ROLAP Snowflake Schema. The stastical implications are the subject of current research, though it's proving a bit of a challenge to account for all the complexities it can entail (code proliferation, data-correctness, increased complexity of refactoring to accomodate changing business rules, data-explosion, etc.). The data-tsunami is upon us :w00t:

    Also, this:

    DECLARE @selectDate = getdate()-365

    won't work. In SQL Server 2008 it needs to be like this:

    DECLARE @selectDate datetime = getdate()-365