Hi all,it's been a while since I've done query tuning and it seems I'm getting quite rusty because there is this one query that has me a little puzzled. And the problem is that the execution plan changes *completely* depending on the value of a parameter that is passed to the query. The following execution plan is used when I filter using "column >= getdate()". This does not return any data and is basically for testing purposes: |--Compute Scalar |--Nested Loops(Inner Join, OUTER REFERENCES:() |--Nested Loops(Inner Join, OUTER REFERENCES:() | |--Compute Scalar(DEFINE:()) | | |--Nested Loops(Inner Join, OUTER REFERENCES:() | | |--Compute Scalar(DEFINE:( | | | |--Nested Loops(Inner Join, OUTER REFERENCES:()) | | | |--Index Seek(OBJECT:(), SEEK:(... >= [@date]) ORDERED FORWARD) | | | |--Clustered Index Seek(OBJECT:(), SEEK:( | | |--Clustered Index Seek(OBJECT:(), SEEK:( | |--Clustered Index Seek(OBJECT:(), SEEK:( |--Clustered Index Seek(OBJECT:(), SEEK:(
While this is what I get when I filter using "column >= DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy, -1, getdate())), 0)" (this means 2009-01-01) which is the "real" filter that I need to use (data from this and the previous year). This query returns some 33k rows.: |--Compute Scalar( |--Merge Join(Inner Join, MERGE:()=(), RESIDUAL:( |--Clustered Index Scan(OBJECT:(), ORDERED FORWARD) |--Sort(ORDER BY:()) |--Hash Match(Inner Join, HASH:()=(), RESIDUAL:( |--Compute Scalar(DEFINE:()) | |--Clustered Index Scan(OBJECT:()) |--Hash Match(Inner Join, HASH:( |--Clustered Index Scan(OBJECT:()) |--Compute Scalar(DEFINE:( |--Clustered Index Scan(OBJECT:(... >=[@date]))
Why is the optimizer doing this when the first plan is obviously the better...?? I'd hate to force the use of a specific plan....- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com
Changing execution plan
Posted - 2010-10-19 : 06:32:44
