Sql-server – Why OUTER APPLY forces scanning the entire table

optimizationsql server

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?

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

Best Answer

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.

Related Solutions

Sql-server – Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

You can do this pretty easily with OUTER APPLY (if you're on 2005 or newer). Note that there may be better performing ways of achieving the result, such as using ROW_NUMBER() - check execution plans if in doubt. Also, SELECT * is lazy and inadvisable; I'm just doing it here for illustrative purposes, and because I don't know the real structure of the Heartbeats table.

SELECT
    dv.Name,
    hb.*
FROM [Devices] as dv
    OUTER APPLY (
        SELECT TOP 1 *
        FROM Heartbeats
        WHERE Heartbeats.DeviceID = dv.ID
        ORDER BY DateEntered DESC
    ) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'

See Books Online for the finer points of OUTER APPLY vs. CROSS APPLY (it's much like OUTER JOIN vs. INNER JOIN). It was always such a pain doing queries like this in SQL Server 2000 where you didn't have OUTER/CROSS APPLY or the ROW_NUMBER() function.

Sql-server – SQL Server pivot filter underlying tables by aliases specified in view

Not as it stands, you will have to add an index for it and that will never be able to scan only the 2011 column from the pivoted view - adding index onto that view is not possible and will give an error Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this view..

Then you need to create an index for the underlying data to get the Pivot operator to run faster. Let's look at if the Optimizer has something to say.

Your sample data is really small so run your query like this.

SELECT ProductID, CountryID, [2011]
 FROM dbo.vw_PivotedTest 
 option (OPTIMIZE FOR UNKNOWN);

Which will optimize the query for a larger set, you can also add GO 50 after the insert statement into the dbo.TestData table to run the insert statements 50 times and thereby adding more data and then you can skip the option part.

You will then get the following index suggestion from the optimizer:

CREATE INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TestData] ([TestID])
INCLUDE ([TimeSeries],[Values]);

Which will help but will scan on the ProductID - which makes sense, you can also create that index like this

CREATE NONCLUSTERED INDEX idx2
ON [dbo].[TestData] ([TestID],[TimeSeries],[Values]);

But you will end up with the same queryplan and more expensive inserts so there is little to gain.

QueryPlanWithAndWithoutInclude

Best Answer

Related Solutions

Sql-server – Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

Sql-server – SQL Server pivot filter underlying tables by aliases specified in view

Related Question