Why OUTER APPLY forces scanning the entire table? I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following select from view.

The upper layer query looks like:

SELECT 
realtyId,    
ServerPath,
OfferState,
CategoryId,
ParentCategoryId
FROM FSTest
WHERE OfferState &lt;= 3 AND (CategoryId = 2 OR ParentCategoryId = 2)
ORDER BY Ranking DESC
OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY


The underlaying view this is following:

CREATE View [dbo].[FSTest] AS
SELECT 
realty.Id as realtyId,
realty.OfferState,
realty.Ranking,
Category.Id as CategoryId,
Category.ParentCategoryId,
ExternFileOuterApply.ServerPath as ServerPath,
ExternFileOuterApply.Height as ImageHeight,
ExternFileOuterApply.Width as ImageWidth
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId =  realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply


The problem is the OUTER APPLY. I would expect that the query skips all 2970 rows and execute the OUTER APPLY only for 30 rows which are contained in the result. But the OUTER APPLY is scanning entire table ExternFile (108864 rows). Why?



Is it somehow connected with temporary index in the index spool (see image bellow)? What is wrong with the query?
Your Scan is running a single time, to populate an Index Spool. This spool is called 3032 times. If you create an index that will suit better, you should be able to avoid the spool, and it probably won't scan any more either.

Edit: But I think what you want is the left-most TOP to be on the right of the Nested Loop. This is a common thing with Key Lookups as well, that they often appear to the right of Top operators when you'd prefer they be on the left. Here, the Spool is quite cheap to query, so there's no much advantage to having it to the side. With a better index, you could probably persuade it not to Spool, and therefore it might see real benefit in pushing the Top further right.

Another edit: Presumably if you left out ServerPath, the Nested Loop disappears completely, as that table is no longer needed at all. But I'm sure you're trying to get it to work without changing the queries much.