5

So we have a simple query that runs with joins to several tables and we date restrict this data. These queries form part of our data warehouse load and are time critical. We noticed a massive difference in both execution speeds and execution plans when we changed from using the dates in a where clause to joining to a temp table with the dates in. so:

    Declare @StartDate DateTime = Cast(Floor(Cast(GetDate() AS FLOAT))AS DateTime)
    Declare @EndDate DateTime = GetDate()

    Select *
    From Table A 
           Inner Join Table B
                On A.A = B.A
    Where A.Date Between @StartDate AND @EndDate

A simplified version of the query but returns 11k rows in around 50secs. 

    Declare @StartDate DateTime = Cast(Floor(Cast(GetDate() AS FLOAT))AS DateTime)
    Declare @EndDate DateTime = GetDate()

    Select @StartDate StartDate, @EndDate EndDate
    Into #Dates

    Select *
    From Table A 
           Inner Join Table B
                On A.A = B.A
           Inner Join #Dates
                On A.Date Between StartDate AND EndDate

Returns the same 11k rows but sub second. The difference in the execution plan is also noticeable, the second query is full of nested loops as opposed to hash matches in the first query.

My question is why? Why the 50 or so second difference?

/* Edit */

The two QEP's

1st Query with dates in Where

2nd Query with dates in join

Improve this question
4
  • This isn't meant to be patronising, sorry, it's just worth a check :) What data type is A.Date? And does it have an index? Commented Feb 23, 2012 at 16:33
  • Its a Datetime, both fields in the where clause are Datetime types. And yes to the index, DTA hadn't suggested any improvements for either query Commented Feb 23, 2012 at 16:43
  • 1
    And how do the plans/execution time vary if you try WHERE a.Date >= @startDate AND a.Date <= @EndDate? I've not encountered your scenario, but then I rarely use BETWEEN (As I normally need < rather than <=) Commented Feb 23, 2012 at 17:01
  • This is very interesting situation. Please let me know @Matt when you find a reason for this! Commented Feb 24, 2012 at 9:22

2 Answers 2

Reset to default
3

I think that what you are running into is an issue of variable sniffing (or lack thereof). It is explained in extreme detail here, but what it comes down to is that when you use local variables, SQL Server is not taking their values into account when building the query plan. When you use a temp table it is, and so it is generating a vastly more performant plan.

A few ways to validate this:

  • Try turning those variables into query parameters (or sproc paremeters)
  • Try adding OPTION(RECOMPILE) as mentioned in the article.

The answer to the question (why the 50s difference) is purely the difference between the efficiency of the good QP vs the bad one.

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

3 Comments

It is being used as a straight up query so we aren't passing any parameters to it just simply setting the value of the parameter. The same QP is returned when putting the date values straight into the where clause
The only other thing that comes to mind is table statistics. Have you updated them? If they are out of date they might suggest that other plan.
I've checked and updated them all across all the tables referenced and still the same QP's and execution time... Really strange behavior!
0

I think it is issue of using functions in WHERE clause. The issue described here

Improve this answer

1 Comment

I don't think this applies. Where are functions being used in the where clause of the OP's examples?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.