Sql View with WHERE clause runs slower than a raw query

This runs in a constant time:

 SELECT row_number() OVER (order by PackagingUniqueId) as RowNum, Barcode, pu.PackagingUniqueId,
rd.Name, pu.ComponentBarcode, rrl.ponum, rrl.mfgpart, rrl.new_lot_code, rrl.pno
    FROM Trace.dbo.TraceData td
    INNER JOIN Trace.dbo.TraceJob tj ON td.Id = tj.TraceDataId
    INNER JOIN Trace.dbo.Job j ON tj.JobId = j.Id
    INNER JOIN Trace.dbo.[Order] o ON j.OrderId = o.id
    INNER JOIN Trace.dbo.PCBBarcode p ON td.PCBBarcodeId = p.Id
    INNER JOIN Trace.dbo.TracePlacement tp ON td.Id = tp.TraceDataId
    INNER JOIN Trace.dbo.Placement p2 ON p2.PlacementGroupId = tp.PlacementGroupId
    INNER JOIN Trace.dbo.Charge c ON p2.ChargeId = c.Id
    INNER JOIN Trace.dbo.PackagingUnit pu ON c.PackagingUnitId = pu.Id
    INNER JOIN Trace.dbo.RefDesignator rd ON p2.RefDesignatorId = rd.Id
    INNER JOIN SpotlightSQL.spot_light_dbo.peel_off_ids po ON po.peel_off_id = pu.PackagingUniqueId
    INNER JOIN SpotlightSQL.spot_light_dbo.recv_receipts_log rrl ON rrl.label_id = po.label_id
    WHERE p.Barcode = '20092619153'

However, this one takes about 7 seconds:

SELECT * FROM Component WHERE Barcode = '20092619153'

Component is a SQL view which consists of the first longer query without WHERE clause.

Why this happens? Does the view retrieve all records and then apply Where clause? Is there a way to speed up the second query? (without applying indexes)

Answer

Why this happens? Does the view retrieve all records and then apply Where clause?

Yes, in this particular case, SQL Server will first execute the original underlying query, and then apply a WHERE filter on top of that intermediate result.

Is there a way to speed up the second query? (without applying indexes)

A SQL view generally performs as well as the underlying query. So, if Barcode is a good way to filter off many records, then adding an index to Barcode is the way to go. Other than this, there is not much you can do to speed up the view.

One option would be to create a materialized view, which is basically just a table whose data is generated by your view’s query. Selecting all records from a materialized view, with no additional restrictions, should have a speed limited only by the time of data transfer.