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
JOIN
acts as a filter as far as reads are concerned and that they should be 9K at most. However, DBA insists that theJOIN
here is not a filter and that the reads are more than 14M because there is noWHERE
clause here.
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?