SQL Execution Plan Bug!
-
Dear Grasshopper,
Appreciating all what you mentioned, it’s all correct, BUT...
My friend: The part “OR @x = -1” is being evaluated for each row in the table! The optimizer should be smart enough to evaluate this part one time only as it will never change between a row and another... this is simply my point.
if you take the part “Table1.Col1 = 5” for example, the result of this condition differs from a row to another,, the rows which have Col1 = 5 will affect the condition to be TRUE, while other values will affect the condition to be FALSE, so for each row in he table the condition should be evaluated or in other words:
This condition is DEPENDENT ,, it depends on each row in the table
This is not valid for the my condition @x = -1 it will be either true or false for all rows.
This condition is INDEPENDENT ,, it does not depend on any row in the table.
I swear I am saying the truth, regardless how Microsoft implemented the optimizer, but it should be smart enough to evaluate the condition for each row or evaluate it one time only.
Regards,
-
I think you didn't read the thing or you just need to have the last word. The plan is created B4 the execution. The plan also has to work for all possible values of the parameters. Also the server doesn't make a plan just in case this value happens an another for all other values. The point of this exercice is to compile the plan once and REUSE IT without compiling everytime. This is where you save a lot of resources on a busy server. If you can't accept that then you can either go dynamic sql or create a query for every possible scenario and then you'll get the most optimal plan everytime.
-
Ten Centuries
I think you did not read what i wrote!
"The plan is created B4 the execution" ok
"The plan also has to work for all possible values of the parameters" ok, for all possible values of the parameter the result will be the same.
Thanks,
-
Hear hear Remi.
Theres a number of good books on the subject, a good start is the list below.
Thomas W Parsons - Introduction to Compiler Construction
Steven Muchnick - Advanced Compiler Design and Implementation
Ken Kennedy, Randy Allen - Optimizing Compilers for Modern Architectures
Robert Paul Corbett - Static semantics and compiler error recovery
There is a lot of info available on the web, just google "static compiler", and you'll get load of links. You could also (if your keen and know a lot about C) get the source code to the GNU C compiler, its not pretty but would show alot of the general techniques in use.
And for rata,
I read what you wrote (as I'm sure Remi did), its just wrong (except for the bits where you agree)
.The @X=-1 is independent of any column in the table, but due to the way the query is written it applies to all rows; not none, not one, but all.
If you don't want the @X = -1 to be applied to any rows in the table, and be evaluated separate from the rest of the query, then you need to remove it from the WHERE clause.
The Query Optimizer is not a mind reader, and isn't there to fix your mistakes.
Garbage in, garbage out.
Try this to see how the Optimizer works.
Rewrite your statement as
IF @X = -1
BEGIN
SELECT C1, C2, C3, C4 FROM Table1
END
ELSE
BEGIN
SELECT C1, C2, C3, C4 FROM Table1 WHERE C1 = @X
END
Display the Estimated Execution Plan (for this statement, this is actually what gets sent to the Relational Engine).
It will show that both statements are evaluated with the appropriate index seek/scan for the query given.
If you actually execute the statement and show the execution plan used, then you will see that the plan used is the one appropriate to the value of @X.
The Optimizer provides the plan to the Relational Engine and the execution stage chooses the appropriate path on the plan based on the value of @X.
The plan shown after execution is the actual path taken through the Execution Plan., it is not the complete plan that was supplied! (very important difference).
With your original query, there is only one path!.
..If you don't like what you see, walk the other way.
-
O'll be sure to get my hands on one of these books. Thanx Nick.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply