SQL Server Performance

Query lots of date columns per record, a better way?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by matt3.5, Sep 9, 2008.

  1. matt3.5 New Member

    Hello,
    I'm querying a table where a lot of dates are stored per row. Each record can have up to 15 nullable dates i.e. productid, dateA, dateB, dateC.....dateO
    This leaves me wondering how best to query this in the most performant way - I need to be able to query on a date range - between @dateLow and @dateHigh. The only thing is, with so many date columns per row, it seems a bit cumbersom on poor ol sql server to have to use an OR pedicate to do something like:
    where dateA between @dateLow and @dateHigh OR dateB between @dateLow and @dateHigh and.....etc etc
    I'm assuming that to check through 15 nullable columns in this manner for each record is not pretty. Is there a more efficient way to do this in terms of actual t-sql, or is my only hope of optimization a covering index or something?
  2. moh_hassan20 New Member

    As you have up to 15 nullable dates :
    solution1: do normalization
    create a new child table date_info to store date information , with fields ( pk , datename,datevalue) , and insert all not null value dates in that table.
    then drop these clumns from the original table.
    study side effect on application that access that table for that change
    solution 2: create a view that union all these fields
    select pk, 'date1 ' datename, date1 from mytable datevalue
    where date1 is not null
    union all
    select pk, 'date2 ' , date2 from mytable
    where date2 is not null
    ....
    ...
    - create index on datename, datevalue on the view
  3. matt3.5 New Member

    I don't think I can normalize any further really, the table I am querying consists of a ProductId column, then the date columns.
    I like the idea of using an indexed view to get rid of the nulls though, I like it a lot!
  4. Adriaan New Member

    That indexed view is the same as the suggested 'normalized' table, really.

Share This Page