Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82

    Unanswered: A very bizarre date/time problem!

    Running into a very bizarre problem here. I'm querying a couple of tables based on a date range. Nothing fancy; simply stuff. The query runs fine when I hard-code the dates, but when I use variables, the query just hangs.

    This works fine:
    Code:
    SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
    FROM A WITH(NOLOCK)
    INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
    INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
    WHERE A.SomeDate>='2008-02-09 00:00:00.000' AND A.SomeDate<'2008-02-09 01:00:00.000'
    AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
    This does NOT: NO clue why
    Code:
    DECLARE   @FromDate DATETIME
    		, @ToDate DATETIME
    
    SELECT @FromDate = '2008-02-09 00:00:00.000'
    SELECT @ToDate = '2008-02-09 01:00:00.000'
    
    SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
    FROM A WITH(NOLOCK)
    INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
    INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
    WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
    AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000

    Any ideas?

    -a

  2. #2
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Update: The A.SomeDate column is indexed. The query with the variables does not make use of the index, where as the query with the hard-coded dates does make use of the index. That's why the query with the variables hangs. I'm able to work around this using a index hint, but am still curious why SQL Server is behaving this way.

  3. #3
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Checked the execution plans on both queries and they are VERY different. Any ideas?

  4. #4
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    FINAL SOLUTION:

    Code:
    DECLARE   @FromDate DATETIME
    		, @ToDate DATETIME
    
    SELECT @FromDate = '2008-02-09 00:00:00.000'
    SELECT @ToDate = '2008-02-09 01:00:00.000'
    
    SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
    FROM A WITH(NOLOCK)
    INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
    INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
    WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
    AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
    OPTION (OPTIMIZE FOR (@FromDate = '2008-02-09 00:00:00.000', @ToDate='2008-02-09 01:00:00.000'))

    I hope this will help someone else experiencing a similar performance issue when using variables.

    -a

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I had similar, strange problems with a query referencing an indexed column in the where clause with NULL values and inconsistent result sets. A rebuild of the index(es) on the table resolved the problem (indexes were not rebuilt for 2 years).
    Last edited by PMASchmed; 02-22-08 at 14:48.

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    I saw the solution and thought I would explain why it works.

    When you use variables, SQL Server doesn't know what values the variables will contain at run time. It can't assume the number of rows within the date range will be small enough for an index seek, so the execution plan will use an index scan instead. If it's a large table, an index scan could take a long time. With the OPTIMIZE FOR hint, SQL server will assume these dates are used and generate an execution plan accordingly, using an index seek.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •