Please refer to the
SELECT query shown below, which includes a
JOIN between two tables that I have renamed as Table1 and Table2, where the latter is a temporary one:
- Table1 has over 15M records, whereas Table2 is around 9K.
- Product team says the
JOINacts as a filter as far as reads are concerned and that they should be 9K at most. However, DBA insists that the
JOINhere is not a filter and that the reads are more than 14M because there is no
My humble opinion (not being a SQL developer or DBA myself) is that the
JOIN can be thought of as a filter in the final resultset, but SQL Server somehow has to read the entire Table1 before it can actually perform the
JOIN with Table2.
The reason that prompted this discussion was a performance issue and the fact that the execution plan shows +14M of actual rows associated with this query:
Unfortunately, this is proprietary code I am not allowed to post publicly. My question is more geared towards what takes place during a SQL
JOIN under the hood. Does SQL Server need to read the entire tables before returning the result set?
The exact question would be: Does it make any sense that SQL Server had to make +14M reads to perform the
JOIN in this scenario?