CODEWITHSUNDEEP
sqlsql-serversubquery
Yesudass Moses
Yesudass Moses

Reputation: 1859

Order of Operation in SQL Server Query

I have the below query selecting items and one of its feature from a hundred thousand row of items.

But I am concerned about the performance of sub query. Will it be executed after or before the where clause ?

Suppose, I am selecting 25 items from 10000 items, this subquery will be executed only for 25 items or 10000 items ?

declare @BlockStart int = 1
    , @BlockSize int = 25
;
select *, (
    select Value_Float
    from Features B
    where B.Feature_ModelID = Itm.ModelID
        and B.Feature_PropertyID = 5
    ) as Price  
from (
    select *
        , row_number() over (order by ModelID desc) as RowNumber
    from Models
) Itm 
where Itm.RowNumber >= @BlockStart
    and Itm.RowNumber < @BlockStart + @BlockSize
order by ModelID desc

Upvotes: 0

Views: 70

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

The sub query in the FROM clause produces a full set of results, but the sub query in the SELECT clause will (generally!) only be run for the records included with the final result set.

As with all things SQL, there is a query optimizer involved, which may at times decide to create seemingly-strange execution plans. In this case, I believe we can be pretty confident, but I need to caution about making sweeping generalizations about SQL language order of operations.

Moving on, have you seen the OFFSET/FECTH syntax available in Sql Server 2012 and later? This seems like a better way to handle the @BlockStart and @BlockSize values, especially as it looks like you're paging on the clustered key. (If you end up paging on an alternate column, the link shows a much faster method).

Also, at risk of making generalizations again, if you can know that only one Features record exists per ModelID with Feature_PropertyID = 5, you will tend to get better performance using a JOIN:

SELECT m.*, f.Value_Float As Price
FROM Models m
LEFT JOIN Features f ON f.Feature_ModelID = m.ModelID AND f.Feature_PropertyID = 5
ORDER BY m.ModelID DESC
OFFSET @BlockStart ROWS FETCH NEXT @BlockSize ROWS ONLY

If you can't make that guarantee, you may get better performance from an APPLY operation:

SELECT m.*, f.Value_Float As Price
FROM Models m
OUTER APPLY (
    SELECT TOP 1 Value_Float 
    FROM Features f 
    WHERE f.Feature_ModelID = m.ModelID AND f.Feature_PropertyID = 5
) f
ORDER BY m.ModelID DESC
OFFSET @BlockStart ROWS FETCH NEXT @BlockSize ROWS ONLY

Finally, this smells like yet another variation of the Entity-Attribute-Value pattern... which, while it has it's places, typically should be a pattern of last resort.

Upvotes: 2

Related Questions