Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
2  
Any open transactions blocking one of the rows covered by that date range? Run it and look in sys.dm_os_waiting_tasks to see if it is blocked waiting on something. –  Martin Smith Jan 12 '12 at 10:16
    
I just checked that, however it seems not to wait on anything. :( –  nojetlag Jan 12 '12 at 13:19
3  
Are you 100% sure that the execution plans for this range are the same then? –  Martin Smith Jan 12 '12 at 13:20
    
Maybe I am missing something obvious but if the dates on your between are the same how can it return any rows? –  JamesRyan Jan 12 '12 at 13:52
1  
@JamesRyan - based on the fact that he has both a TIME and DATE field, he is either using a date datatype (notice he is using 2008r2) or these are string fields –  JNK Jan 12 '12 at 15:38
show 3 more comments

4 Answers 4

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.

share|improve this answer
    
Thanks very much for your feedback, since this query is used to feed a dimension table, I need to use distinct and the functions. Since this query should only insert the ones that are not already existing in the dimension table I have to use the lookup. (The data goes into millisecond) Records are 45351 for 8.1.2012 and 482096 for 09.01.2012(which runs without a problem). DBCC CHECKDB runs once day and never indicated any problems. –  nojetlag Jan 16 '12 at 9:18
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.