3

I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by myself)

So, simple question: is SQL Server short circuiting JOIN based on the JOIN condition? If I write:

TableA LEFT OUTER JOIN TableB ON 1=0 AND TableA.ID = TableB.A_ID

will SQL Server skip the join at all?


Edit

Ok, someone correctly pointed out that this is solved at compile time, so I'd better expand on the question.

The above was just intended as an example, but it turned out to be a bad one. What I wanted to know is if SQL Server shorts circuit a JOIN when that condition happens at runtime, as a conseguence of a @parameter passed to a stored procedure.

I'm asking 'cause I'm sure to have used something like that back in the years, but I wanted to be 100% sure before talking about it with some colleagues. So, will SQL Server short circuit this, then:

TableA LEFT OUTER JOIN TableB ON @parameter IS NOT NULL AND TableA.ID = TableB.A_ID

if it's inside an SP and it's called with @parameter set to NULL? Will it make any difference if the store procedure is WITH RECOMPILE?

  • 3
    Under what circumstances would you do this? If your real situation is talking about a condition that isn't using constant values and the condition can't be proved false at compile time the answer may be different. – Martin Smith May 27 '18 at 12:54
  • 2
    What @MartinSmith said. Sometimes you'll have situations where runtime conditions lead to an operator being "skipped" - it's still there in the plan, but there are zero actual executions (meaning no work and no table access). – Aaron Bertrand May 27 '18 at 14:13
  • @MartinSmith that is the reason I was waiting to accept the answer as correct. I'll edit the question. – motoDrizzt May 27 '18 at 17:32
  • Query optimizer gets to decide and it will often parallelize. – paparazzo May 27 '18 at 19:01
  • @MartinSmith yeah, I shouldn't be doing 10 things at the same time, ok – motoDrizzt May 27 '18 at 19:26
3
  1. will SQL Server short circuit this, then: TableA LEFT OUTER JOIN TableB ON @parameter IS NOT NULL AND TableA.ID = TableB.A_ID if it's inside an SP and it's called with @parameter set to NULL?
  2. Will it make any difference if the store procedure is WITH RECOMPILE

Data Setup

CREATE TABLE TableA(ID INT PRIMARY KEY);
CREATE TABLE TableB(A_ID INT);

INSERT INTO TableA VALUES (1), (2), (3);

INSERT INTO TableB VALUES (1), (1), (1), (1), (1), (1), (2), (3);

Stored proc

CREATE PROC P1 @parameter INT
AS
    SELECT *
    FROM   TableA
           LEFT OUTER LOOP JOIN TableB L
             ON @parameter IS NOT NULL
                AND TableA.ID = L.A_ID
           LEFT OUTER MERGE JOIN TableB M
             ON @parameter IS NOT NULL
                AND TableA.ID = M.A_ID
           LEFT OUTER HASH JOIN TableB H
             ON @parameter IS NOT NULL
                AND TableA.ID = H.A_ID; 

The plan for this after executing EXEC P1 1 looks as follows.

enter image description here

  • All of the join types are able to put a filter with a startup predicate above the leaf operator accessing table B. Meaning at runtime it is only scanned if needed.
  • The actual and estimated rows are spot on for this example.

However the estimated rows depend on which parameter value is passed when the plan is compiled. If NULL had been passed the estimated number of rows in the final operator is 1.37074. In this simple plan the fact that 218 rows can be emitted for a subtree with estimated 1.37 rows probably won't make much difference but it could do if this was part of a larger query. And even in this simple example it could mean memory grants for the final hash join are incorrect.

Logically an outer join can't reduce the number of rows but for some reason the initial 3 gets scaled down to 1.37074 after passing through the two other outer joins here.

If somewhat accurate cardinality estimates are required then WITH RECOMPILE does solve this issue - the cardinality estimates are either 1.37074 or 218 dependant on the nullability of the parameter. But OPTION (RECOMPILE) would be preferable. As well as being targeted at the specific statement it also allows additional simplifications to be made.

With OPTION (RECOMPILE) in place the execution plan when calling with NULL is as below.

enter image description here

The actual and estimated rows are spot on and all redundant operators have been removed from the plan (so it won't spend time creating build input for the unneeded hash join in this case).


Plans above generated on build 14.0.1000.169

  • Thx man. I had a chance to test it myself this morning at work and got the same conclusions (that is, it works with OPTION (RECOMPILE) ), but you managed to put down a very high quality answer, you too have my upvote and I'll mark the answer as accepted in a while. Thx again :-) – motoDrizzt May 28 '18 at 11:44
6

DB<>fiddle and other online-services allow you to test different DBMS without having to install them locally. As shown in:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7692d5abba854bdc57d90c95335014c0

There is no access to table B.

You may find the following article by Lukas Eder of interest, he compares algebraic optimizations for different DBMS:

https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.