Unexpected performance issue with T-SQL Query

Question

I have the following T-SQL Query:

SELECT Wk.WeekID,
       Wk.WeekStart,
       SUM(CASE WHEN NOT(Task.WeekDate IS NULL) THEN 1 ELSE 0 END) AS WeekCount,
       SUM(CASE WHEN NOT(Task.DayDate IS NULL) THEN 1 ELSE 0 END) AS DayCount
FROM tblPerWeek AS Wk
LEFT OUTER JOIN tblPerTask AS Task ON (Task.WeekDate = Wk.WeekStart) 
                                   OR (Task.DayDate BETWEEN Wk.WeekStart AND Wk.WeekEnd)
WHERE (Wk.WeekStart <= @DateEnd) AND (Wk.WeekEnd >= @DateStart)
GROUP BY Wk.WeekID, Wk.WeekStart

The structure of tblPerWeek table:

WeekID int
WeekStart date
WeekEnd date

The structure of tblPerTask table:

TaskID int
WeekDate date
DayDate date

Basically, the query counts tasks assigned to week (WeekCount) or specific day inside week (DayCount).

The tblPerWeek has about 2800 records and tblPerTask has about 70000 records.

Now, there is some problem/glitch with (Task.DayDate BETWEEN Wk.WeekStart AND Wk.WeekEnd) condition in join:

Without this condition in join the query completes instantly
With this condition in join the query takes about 12 seconds to run

What is the catch? Any solutions on how to make this query faster?

Yes, I have put one index per one date column in both tables, no luck. — Dusan
– Dusan, Commented Aug 20, 2015 at 20:58
Your join condition involves an or so that's not going to be good. Is the first half of it redundant? — shawnt00
– shawnt00, Commented Aug 20, 2015 at 20:58
Maybe replace (Wk.WeekStart <= @DateEnd) AND (Wk.WeekEnd >= @DateStart) with Wk.WeekStart between dateadd(dd, -6, @DateStart) and @DateEnd ?? Looks like you're trying to catch partial weeks or something but I'm not really sure. — shawnt00
– shawnt00, Commented Aug 20, 2015 at 21:02
The @DateStart and @DateEnd occupy more than one week, in fact, i was testing 50 years range. — Dusan
– Dusan, Commented Aug 20, 2015 at 21:03

HLGEM · Accepted Answer · 2015-08-20 21:05:34Z

1

Typically the problem of using OR is solved by using a UNION query instead. If the two groups of records will be mutually exclusive use UNION ALL which is even faster.

SELECT Wk.WeekID,
       Wk.WeekStart,
       SUM(CASE WHEN NOT(Task.WeekDate IS NULL) THEN 1 ELSE 0 END) AS WeekCount,
       SUM(CASE WHEN NOT(Task.DayDate IS NULL) THEN 1 ELSE 0 END) AS DayCount
FROM tblPerWeek AS Wk
LEFT OUTER JOIN tblPerTask AS Task ON Task.WeekDate = Wk.WeekStart
WHERE (Wk.WeekStart <= @DateEnd) AND (Wk.WeekEnd >= @DateStart)
GROUP BY Wk.WeekID, Wk.WeekStart
UNION 
SELECT Wk.WeekID,
       Wk.WeekStart,
       SUM(CASE WHEN NOT(Task.WeekDate IS NULL) THEN 1 ELSE 0 END) AS WeekCount,
       SUM(CASE WHEN NOT(Task.DayDate IS NULL) THEN 1 ELSE 0 END) AS DayCount
FROM tblPerWeek AS Wk
LEFT OUTER JOIN tblPerTask AS Task ON Task.DayDate BETWEEN Wk.WeekStart AND Wk.WeekEnd
WHERE (Wk.WeekStart <= @DateEnd) AND (Wk.WeekEnd >= @DateStart)
GROUP BY Wk.WeekID, Wk.WeekStart

answered Aug 20, 2015 at 21:05

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

shawnt00 Over a year ago

I don't think union by itself would work. You'll probably have to use union all and then aggregate again.

HLGEM Over a year ago

Check and see if all of the records in part 1 are also in part two. It is possible that you only need the second query.

shawnt00 Over a year ago

The way the summations are done makes me think there are two different types of tasks being handled by the join and that there is no overlap.

Dusan Over a year ago

In one task only one of WeekDate or DayDate is filled (never both of them)

Dusan Over a year ago

The wierd thing that I have now tried ISNULL(Task.WeekDate, Task.DayDate) BETWEEN Wk.WeekStart AND Wk.WeekEnd and makes same performance horror.

|

paparazzo · Accepted Answer · 2015-08-20 22:18:03Z

1

optimizer hates OR

SELECT Wk.WeekID,
       Wk.WeekStart,
       SUM(CASE WHEN TaskW.WeekDate IS NULL THEN 0 ELSE 1 END) AS WeekCount,
       SUM(CASE WHEN TaskD.DayDate  IS NULL THEN 0 ELSE 1 END) AS DayCount
FROM tblPerWeek AS Wk
LEFT OUTER JOIN tblPerTask AS TaskW ON TaskW.WeekDate = Wk.WeekStart                                       
LEFT OUTER JOIN tblPerTask AS TaskD ON TaskD.DayDate BETWEEN Wk.WeekStart AND Wk.WeekEnd
WHERE Wk.WeekStart <= @DateEnd AND Wk.WeekEnd >= @DateStart
GROUP BY Wk.WeekID, Wk.WeekStart

and I think you can just do a count as a count null is 0

SELECT Wk.WeekID, Wk.WeekStart,
       COUNT(TaskW.WeekDate) AS WeekCount,
       COUNT(TaskD.DayDate)  AS DayCount
FROM tblPerWeek AS Wk
LEFT OUTER JOIN tblPerTask AS TaskW ON TaskW.WeekDate = Wk.WeekStart                                       
LEFT OUTER JOIN tblPerTask AS TaskD ON TaskD.DayDate BETWEEN Wk.WeekStart AND Wk.WeekEnd
WHERE Wk.WeekStart <= @DateEnd AND Wk.WeekEnd >= @DateStart
GROUP BY Wk.WeekID, Wk.WeekStart

edited Aug 20, 2015 at 22:18

answered Aug 20, 2015 at 21:26

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

2 Comments

Dusan Over a year ago

You know, this is the first thing I tried before even posting question, but probably used only TaskW in both SUM and also worked slow as hell. But now, I have copy/pasted your query and works like a rocket!

Dusan Over a year ago

Unfortunately, this kind of joins produces kind of cross join between tasks and counts are all wrong.

Collectives™ on Stack Overflow

Unexpected performance issue with T-SQL Query

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related