OFFSET and FETCH causing massive performance hit on a query - including when OFFSET = 0

Question

I have a fairly complex SQL query that involves returning about 20 columns from a large number of joins, used to populate a grid of results in a UI. It also uses a couple of CTEs to pre-filter the results. I've included an approximation of the query below (I've commented out the lines that fix the performance)

As the amount of data in the DB increased, the query performance tanked pretty hard, with only about 2500 rows in the main table 'Contract'.

Through experimentation, I found that by just removing the order, offset fetch at the end the performance went from around 30sec to just 1 sec!

order by 1 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

This makes no sense to me. The final line should be pretty cheap, free even when the OFFSET is zero, so why is it adding 29secs on to my query time?

In order to maintain the same function for the SQL, I adapted it so that I first select into #TEMP, then perform the above order-offset-fetch on the temp table, then drop the temp table. This completes in about 2-3 seconds.

My 'optimisation' feels pretty wrong, surely there's a more sane way to achieve the same speed?

I haven't extensively tested this for larger datasets, it's essentially a quick fix to get performance back for now. I doubt it will be efficient as the data size grows.

Other than the Clustered Indexes on the primary keys, there are no indexes on the tables. The Query Execution plan didn't appear to show any major bottlenecks, but I'm not an expert on interpreting it.

WITH tableOfAllContractIdsThatMatchRequiredStatus(contractId) 
AS (
    SELECT DISTINCT c.id
    FROM contract c 
    INNER JOIN site s ON s.ContractId = c.id
    INNER JOIN SiteSupply ss ON ss.SiteId = s.id AND ss.status != 'Draft'
    WHERE 
        ISNULL(s.Deleted, '0') = 0 
        AND ss.status in ('saved')
)
,tableOfAllStatusesForAContract(contractId, status) 
AS (
    SELECT DISTINCT c.id, ss.status
    FROM contract c 
    INNER JOIN site s ON s.ContractId = c.id
    INNER JOIN SiteSupply ss ON ss.SiteId = s.id AND ss.status != 'Draft'
    WHERE ss.SupplyType IN ('Electricity') AND ISNULL(s.Deleted, '0') = 0 
)

SELECT 
     [Contract].[Id]
    ,[Contract].[IsMultiSite]
    ,statuses.StatusesAsCsv
    ... lots more columns
    ,[WaterSupply].[Status] AS ws

--INTO #temp

FROM 
(
    SELECT 
        tableOfAllStatusesForAContract.contractId, 
        string_agg(status, ', ') AS StatusesAsCsv  
    FROM 
        tableOfAllStatusesForAContract
    GROUP BY 
        tableOfAllStatusesForAContract.contractId
) statuses

JOIN contract ON Contract.id = statuses.contractId
JOIN tableOfAllContractIdsThatMatchRequiredStatus ON tableOfAllContractIdsThatMatchRequiredStatus.contractId = Contract.id
JOIN Site ON contract.Id = site.contractId and site.isprimarySite = 1 AND ISNULL(Site.Deleted,0) = 0
... several more joins
JOIN [User] ON [Contract].ownerUserId = [User].Id

WHERE isnull(Deleted, 0) = 0 
AND
 (
 [Contract].[Id] = '12659' 
 OR [Site].[Id] = '12659'
 ... often more search term type predicates here
  )

--select * from #temp
order by 1
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
--drop table #temp

Answer 1

I've not had an answer, so I'm going to try and explain it myself, with my admittedly poor understanding of how SQL works and some pointers from Jeroen in comments above. It's probably not right, but from what I've discovered it could be correct, and I do know how to fix my immediate problem so it could help others.

I'll explain it with an analogy, as this is what I believe is probably happening:

Imagine you're a chef in a restaurant, and you have to prepare a large number of meals (rows in results). You know there's going to be a lot as you're front of house has told you this (TOP 10 or FETCH 10).

You spend time setting out the multitude of ingredients required (table joins) and equipment you'll need and as the first order comes in, you make sure you're going to be really efficient. Chopping up more that you need for the first order, putting it in little bowls ready to use on the subsequent orders. The first order takes you quite a while (30 secs) as you're planning ahead and want the subsequent dishes to go out as fast as possible.

However, as you're sat in the kitchen waiting for the next orders.. then don't arrive. That's it, just one order. Well that was a waste of time! If you'd just tried to get one dish out, you could have done it much faster (1sec) but you were planning ahead for something that was never needed.

The next night, you ditch your previous strategy and just do each plate at a time. However this time, there are 100s of customers. You can't deliver them fast enough doing them one at a time. The amount of time to deliver all the orders would have been much faster if you'd planned ahead like the previous night. (I've not tested this hypothesis, but I expect it is what would probably happen).

For my query, I don't know if there's going to be 1 result or 100s although I may be able to do some analysis up front based on the search criteria entered by the user, I may have to adapt my UI to give me more information so I can predict this better, which means I can pick the appropriate strategy for SQL to use upfront. As it is, I'm optimised for a small number of results which works fine for now - but I need to do some more extensive testing to see how performance is affected as the dataset grows.

"If you want a answer to something, post something that's wrong on the internet and someone will be sure to correct you"