SQL Server Performance

Slicker way to select records from a history table?

Discussion in 'SQL Server 2008 General Developer Questions' started by hominamad, Oct 28, 2010.

  1. hominamad Member

    Hi - in my application we have several historical tables that have a column such as "AsOfDate" and store snapshots of the data on certain dates. We then have many queries where we need to query what the data looked like as of certain points in time. I know there are several ways to do this, but was wondering if anyone had a more clever way.
    The most common way we do it is something like this:
    select *
    from MyTable t inner join (
    select SomeID, MAX(AsOfDate)
    from MyTable t2
    where AsOfDate <= @AsOfDate) on t.SomeID = t2.SomeID and t.AsOfDate = t2.AsOfDate
    Ideally, I'm looking for a way to do with without having to do some sort of sub-query as above - for performance and readability reasons. It would be nice if I could do something like below, but SQL Server doesn't allow it:
    select *
    from MyTable
    where ROW_NUMBER() over (partition by SomeID, AsOfDate order by AsOfDate desc) = 1
    I know you can also use a CTE or a derived table, but again, looking for a clever way to do it "on the fly". Anyone have any ideas?
  2. FrankKalis Moderator

    I've been experiementing with this as well and have come to the conclusion that nothing beats the subquery (SELECT MAX(..)...) approach in terms of performance. Especially when your tables grow larger (the one here on my side is currently +350 million and growing strong). A CTE with Windowing functions just doesn't seem to scale to these regions. [:(]
    As for readability: I don't think it is that bad if you lay it out nicely. One thing I haven't tried yet, is to encapsulate the MAX() subquery into an inline table-valued function that you just pass the @AsOfDate to. Would certainly make it also more readable, but I can't tell what the effect on performance would be. I would guess that it doesn't make a difference and that the same plan is generated, but I haven't verified this myself.
  3. Adriaan New Member

    You could add an indexed view on SomeID, MAX(AsOfDate) and use that instead of a derived table.
  4. hominamad Member

    Wow - I'm lucky I don't have to deal with 350 million records. I'm well under 1million in most cases. Another thing I was thinking about doing - most of the time we are interested in getting the latest record - as opposed to looking back in time. I was thinking of putting a column called something like IsLatest - and having a trigger manage this column - seeing it to true if that record is the latest version. Then in my selects, I can just do WHERE IsLatest=1.
  5. Adriaan New Member

    Indexed view would be self-maintaining.
  6. hominamad Member

    But then I still have to do a join. If I make it an indexed column I can just select from one table. For getting records as of prior dates, the indexed view sounds like a reasonable option though.
  7. Adriaan New Member

    Index would only make sense as (SomeID, IsLatest)
  8. FrankKalis Moderator

    Well, that would be an option to simplify the query, and you would have to test this in your environment and given your workload if you are happy with the performance. But from the logical perspective it somehow just doesn't "sound right". I mean I wouldn't add the extra column, introduce the trigger, maybe incur a performance hit just to save some lines of code and maybe a JOIN. But ymmv and it is certainly a feasable option.

Share This Page