3

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 <= 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?

enter image description here

Is it somehow connected with temporary index in the index spool (see image bellow)? What is wrong with the query?

1
  • 2
    How about posting the entire table definition, including all indexes? – Remus Rusanu Oct 17 '15 at 21:24
4

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.

2
  • What is the Compute Scalar doing? It feels to me like there is a problem with what's in the index you have an the index that it wants (and creates in the spool). – Rob Farley Oct 17 '15 at 23:08
  • Yes, ifif you left out ServerPath, the Nested Loop disappears. I presume that it could seek that 30 rows with another index. It seems, the optimizer decided to scan entire table, create spool to seek that 30 rows faster. After providing better index for ExternFile, this Index spool disappears. But still I can see number of execution is 3000. I would expect only 30. I hope I can figure it out later. – Tomas Kubes Oct 18 '15 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.