patternsqlModerate
Does SQL guarantee that `SELECT *` in a subquery will be optimized to prevent a full scan?
Viewed 0 times
preventguaranteefullscansqloptimizedsubquerythatwilldoes
Problem
I am exploring extended events query tracking and have question on couple of odd queries like following:
Example 1:
Example 2:
So in the subquery it selects all rows. And then in outer query it applies the where criteria.
If it literally works like this then it means the subquery will do the full table scan and then apply the where clause to the result. Or does this query gets optimized so that the where clause is applied directly on the table?
Example 1:
select top 10 * from ( SELECT [id] ,[date] ,[ordnum] ,
[customer] ,[amt] ,[gm pc] FROM [DbName].[dbo].[tblSales] ) as [_]
where [date]>='2022-01-01T00:00:00.000'Example 2:
select [_].[id], [_].[date], [_].[ordnum], [_].[customer], [_].[amt],
[_].[gm pc]
from ( SELECT [id] ,[date] ,[ordnum] , [customer] ,[amt] ,[gm pc]
FROM [DbName].[dbo].[tblSales] ) as [_] where [_].[date] >=
convert(datetime2, '2020-01-01 00:00:00')
and [_].[date] < convert(datetime2, '2021-01-01 00:00:00')So in the subquery it selects all rows. And then in outer query it applies the where criteria.
If it literally works like this then it means the subquery will do the full table scan and then apply the where clause to the result. Or does this query gets optimized so that the where clause is applied directly on the table?
Solution
The SQL text expresses the logical results needed. SQL Server's cost-based optimizer finds an efficient physical way of implementing that logical requirement. The results are guaranteed to be the same (for all possible data values) as specified by the SQL, of course.
One of the basic things the optimizer does is to push filtering conditions (predicates) as far down (toward the leaves of) the execution plan as possible.
So yes, for all practical purposes, examples like yours are 'guaranteed' not to execute literally with a full scan followed by a filter. The filtering condition will normally be evaluated as part of the scan, or better yet as an index range seek if a suitable index is available.
If it literally works like this then it means the subquery will do the full table scan and then apply the where clause to the result.
You still seem to be thinking of execution plans as if each operator executes to completion before passing the resulting rows to the next operator. It does not work that way. See my article Iterators, Query Plans, and Why They Run Backwards for details.
One of the basic things the optimizer does is to push filtering conditions (predicates) as far down (toward the leaves of) the execution plan as possible.
So yes, for all practical purposes, examples like yours are 'guaranteed' not to execute literally with a full scan followed by a filter. The filtering condition will normally be evaluated as part of the scan, or better yet as an index range seek if a suitable index is available.
If it literally works like this then it means the subquery will do the full table scan and then apply the where clause to the result.
You still seem to be thinking of execution plans as if each operator executes to completion before passing the resulting rows to the next operator. It does not work that way. See my article Iterators, Query Plans, and Why They Run Backwards for details.
Context
StackExchange Database Administrators Q#322999, answer score: 13
Revisions (0)
No revisions yet.