CODEWITHSUNDEEP
sql-server
Randy Minder
Randy Minder

Reputation: 48402

Query Optimization 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.

Upvotes: 1

Views: 82

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40289

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.)

Upvotes: 3

Related Questions