November 5, 2017 at 2:26 pm
Hi all,
Got a bit of a theoretical question. Suppose I have a relatively complex query, involving many JOINs to other tables. Now, suppose the root table of the query has a condition attached to it, which can be evaluated through an INDEX SEEK or CLUSTERED INDEX SEEK operation. For example,
SELECT [VALS]
FROM Table1
JOIN Table2 ON [CONDITIONS]
JOIN Table3 ON [CONDITIONS]
WHERE Table1.ID = 1 AND Table1.Val = 0
Now, suppose in this case, Table1.ID is a PRIMARY KEY, and the condition Table1.Val = 0 evaluates to FALSE - IE, the root table part of the query returns zero records. This means, of course, that the JOINed tables are irrelevant, since you're JOINing from a source of zero records.
Does the query engine / optimizer still have to do any work evaluating the other parts of the query? Or is it smart enough to pick up the fact that the root part of the query evaluated to zero records, and therefore the rest of the query is irrelevant? Does it matter how complex the rest of the query is? Or whether there are subqueries involved, such as using APPLY statements, or potentially even UDF calls?
November 5, 2017 at 6:45 pm
One fundamental requirement that the optimiser has is that plans must be safe for reuse, since plans are cached and reused. So any plan generated for that query MUST return correct results if it is reused at a point after a row with Val = 0 has been inserted. Hence it must optimise and evaluate the entire query, and produce a plan that is always valid.
The only time it can depend on a predicate matching 0 rows is when it's something that cannot be true like checking for NULLs in a NOT NULL column, or when there's a logical impossibility like WHERE 1 = 0;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply