Query Performance depending on parameter 21seconds vs. > 14hours

Question

I have the following query:

SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))),
                [DATE],
                DATEPART(YY,[DATE]),
                DATEPART(MM,[DATE]),
                DATENAME(MONTH,[DATE]),
                DATEPART(DD,[DATE]),
                [Time],
                DATEPART(HH,[Time]),
                DATEPART(MI,[Time]),
                DATEPART(SS,[Time])
FROM [SourceTable]
WHERE ([DATE] BETWEEN '2012-01-08' AND '2012-01-08') AND
       DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))) NOT IN
       (SELECT [DateTime]
        FROM [DIM].[DateTime] 
        WHERE [Date] BETWEEN '2012-01-08' AND '2012-01-08')
ORDER BY [DATE], [Time]
OPTION (MAXDOP 6)

When I use this specific date I had to cancel the query after 14hours running time, any other date the same query takes normally 21 seconds. I did check the query execution plan independent on what date I use it is the same, the amount of rows impacted by the query is for each day nearly the same (around 500'000 Rows). I checked the indexes and the statistics are up to date. How is such a behaviour explainable and how to fix this ?

The Hardware of the machine is sufficient (8 Cores, 192 GB RAM)

Answer 1

You may want to look at how your data is stratified. Try something like:

SELECT [date], COUNT(*)
FROM MyTable
GROUP BY [Date]

To get an idea. It's possible that you have a very high incidence of records on that date, making the indexes not very selective and causing table scans.

It's also possible you have an issue with a particular file or some data pages that only occur on that date. You can also try:

DBCC CHECKDB

To see if you get any errors.

My guess is you just have a large number of records for that date. This is a pretty inefficient query to begin with:

• You use DISTINCT
• You use EIGHT function calls in the SELECT list
• In your WHERE clause you compare another function result to a subquery using an exclusion (NOT IN)
• You ORDER BY two fields

DISTINCT is pretty inefficient at best. With this many fields, and most of the fields being non-sargable, it's extremely inefficient.

Having a function in your WHERE clause normally forces a tablescan. The engine can't know what the result of the function is without running it for each row, so it needs to check every single row.

The NOT IN also forces a check of every single row - since we don't know what we want to include, only what to EXCLUDE, every row needs to be checked, and compared to every row in the subquery.

The ORDER BY will also force a sort in tempdb of the result set.

Answer 2

I don't know about sqlserver but in an Oracle database, I would suggest making a trace of the sql execution, one that includes all waits and events that cause the query to spend time. This shows the exact circumstances where the sql is executing in and that might be very different than those in the environment where you did the explain plan. Sqlserver without doubt has a similar feature to show the real execution, including waits.

In Oracle we have sql plan stability. Maybe sqlserver has something similar? In that case, try to use that.

Answer 3

I could fix it now with the following approach:

SELECT [DateTime]
INTO #DateTime
FROM [DIM].[DateTime] 
WHERE [Date] BETWEEN '2012-01-08' AND '2012-01-08'
ORDER BY [DATE] DESC

IF (SELECT COUNT (*) 'Records in DIM' FROM #DateTime ) > 0
BEGIN
      INSERT INTO [DIM].[DateTime]
      SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))),
                             [DATE],
                             DATEPART(YY,[DATE]),
                             DATEPART(MM,[DATE]),
                             DATENAME(MONTH,[DATE]),
                             DATEPART(DD,[DATE]),
                             [Time],
                             DATEPART(HH,[Time]),
                             DATEPART(MI,[Time]),
                             DATEPART(SS,[Time])
      FROM [SourceTable]
      WHERE ([DATE] BETWEEN '2012-01-08' AND '2012-01-08') AND
               DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))) NOT IN
               (SELECT [DateTime] FROM #DateTime)
      ORDER BY [DATE], [Time]
      OPTION (MAXDOP 6)
END
ELSE
BEGIN
-- if no records in DIM 
      INSERT INTO [DIM].[DateTime]
      SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))),
                             [DATE],
                             DATEPART(YY,[DATE]),
                             DATEPART(MM,[DATE]),
                             DATENAME(MONTH,[DATE]),
                             DATEPART(DD,[DATE]),
                             [Time],
                             DATEPART(HH,[Time]),
                             DATEPART(MI,[Time]),
                             DATEPART(SS,[Time])
      FROM [SourceTable]
      WHERE ([DATE] BETWEEN '2012-01-08' AND '2012-01-08') AND
               DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))) NOT IN
               (SELECT [DateTime]
                FROM [DIM].[DateTime] 
                WHERE [Date] BETWEEN '2012-01-08' AND '2012-01-08')
      ORDER BY [DATE], [Time]
      OPTION (MAXDOP 6)
END

DROP TABLE #DateTime

Answer 4

It seems to me you are trying to generate a date dimension reference table based on the available dates and times within the [SourceTable] for a specific date, a day at a time.

An alternative that you may want to examine is to effetively 'hard code' the generation of the date dimension rows, as long as you are happy that every day/minute/second combination is required.

Example: The T-SQL below creates a days worth of date dimension data at 'to the second' granularity.

drop table #dim_date_60
drop table #dim_date_24


SELECT  TOP 60
    IDENTITY(int, 0,1) AS ordinal
INTO    #dim_date_60
FROM    sys.objects

SELECT  TOP 24
    IDENTITY(int, 0,1) AS ordinal
INTO    #dim_date_24
FROM    sys.objects


-- One of the few times that a crossjoin can be really useful... 60 * 60 * 24
select 
    DATEADD(hour, c.ordinal,
    DATEADD(minute, b.ordinal,
        DATEADD(second, a.ordinal, '2012/12/1') ) ) 
            as dim_date
into    #dim_date
from    #dim_date_60        a
    ,#dim_date_60       b
    ,#dim_date_24       c   

-- finally insert these records into your date dimension table