DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys.all_objects o1, sys.all_objects o2, sys.all_objects o3;
WITH agg AS (SELECT DISTINCT a FROM #BigTable) SELECT * FROM #EmptyTable E INNER HASH JOIN agg B ON B.A = E.A;
This is a simplified repro for a phenomenon I hadn't noticed before today. My expectation for an inner hash join would be that if the build input is empty the probe side should not be executed as the join can return no rows. The above example contradicts that and reads the 10 million rows from the table. This adds 2.196 seconds to the execution time of the query (99.9%).
OPTION (MAXDOP 1)the execution plan reads no rows from
0for all operators on the inside of the hash join.
- For the query
SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A- I get a parallel plan, the scan operator on the inside of the hash join does have
ActualExecutionsof DOP but still no rows are read. This plan has no repartition streams operator (or aggregate)
What's going on here? Why does the original plan exhibit the problem and the other cases don't?