Post reply

Impossible query performance only when criteria includes "today" date/times

  • June 30, 2010 at 2:01 pm

    #237163

    I have a Photo database for a newspaper company. The main Photo table has about 700K rows.

    One of this table's columns is "CreatedDate" AS SmallDateTime.

    When Performing a search, I provide a search criteria for the user to request photos from a specific day, which results in a WHERE clause similar to

    (CreatedDate>='20100630 00:00:00') AND (CreatedDate<='20100630 23:59:00' OR CreatedDate IS NULL)

    If this WHERE clause is used for a date other then the current date, it performs the query in a few milliseconds. If the query uses the date of "today" (in the previous example, "today" is 20100630 - June, 30th) the query takes more then 20 minutes to perform.

    Desperately seeking for an answer I found out the query performs well until the criteria reaches 08:47 ((CreatedDate>='20100630 00:00:00') AND (CreatedDate<='20100630 08:47:00' OR CreatedDate IS NULL)), after that it starts misbehaving.

    I thought it was related to the index statistics needing update but updating index statistics didn't make any difference.

    There seems to be no connection to any procedures ran by any maintenance plan.

    Do you have any ideas?

    Thanks for your help!

    All the best,

    Joel

  • June 30, 2010 at 2:14 pm

    #1187324

    Not an answer, but a place to look...

    Have you noticed if the Query Execution plan changes between the two cases -- the one where performance is good, and the one (after 8:47 today) where it starts to perform poorly?

    That might give you some clues.

    Rob Schripsema
    Propack, Inc.

  • June 30, 2010 at 2:21 pm

    #1187327

    Joel Paula-461376 (6/30/2010)

    I thought it was related to the index statistics needing update but updating index statistics didn't make any difference.

    Did you update with fullscan? If not, try that. This is a classic symptom of stale/inaccurate stats.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 30, 2010 at 2:28 pm

    #1187330

    Thanks very much for your response. That solved it.

    Do I have to perform a update statistics with fullscan every hour or something? How can I prevent this problem?

  • June 30, 2010 at 3:23 pm

    #1187356

    Maybe. How fast does data get added to the table? How long before queries of latest rows start to go bad?

    Is a common problem with queries that filter for latest date on ascending datetime columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply