Query Optimization Question

Question

Assume I have the following query:

Update LPMBonusReport Set BoxID = (Select ContainerSerialNumber 
       From Wip.vwWorkItem Where SerialNumber = LPMBonusReport.SubID)

The object Wip.vwWorkItem is a view.

Assume there are 100 rows in the LPMBonusReport table. Would the view get materialized 100 times, once for each row in LPMBonusReport, or would it get materialized just once?

Thanks.

Answer 1

The optimizer would build a (single) execution plan based on LPMBonsReport and the tables comprising the view. Run and review your query in an SSMS query window with "Include Actual Execution Plan" on (it's an option in the "Query" menu.)

Answer 2

I'd prefer to eliminate the subquery and see this written as:

Update BR
    Set BoxID = WI.ContainerSerialNumber
    from LPMBonusReport BR
        inner join Wip.vwWorkItem WI
            on BR.SubID = WI.SerialNumber