Sql-server – SQL Query taking long to execute especially with offset and fetch for pagination

performancesql serversql server 2014

This is the query I'm running for a report. It takes about 20 seconds to run without the offset and fetch. With those two, The query doesn't even complete in 10 minutes.

select *
-- h.ActionDate as Date, h.Action, h.Actor , h.PaymentLotNo 
-- , sh.OwnerName as OwnersName,sh.TECHID as TechId, sh.Id as SubsidyCardId, sh.TotalSubsidy as SubsidyAmount
-- ,a.ToleEng + '-' + v.NameEng + cast(a.Ward as nvarchar(4)) + ',' + d.NameEng + ',' + s.StateNameEng as Address
from common.CTB_SUBSIDY_APPLICATION_HISTORY h 
join fsams.CTB_SUBSIDY_CARD_HEADER sh on sh.LotNumber = h.PaymentLotNo 
join fsams.CTB_SUBSIDY_CARD_ADDRESS sa on sa.SubsidyId = sh.Id 
join common.CTB_ADDRESS a on a.AddressID = sa.AddressId 
join COMMON.CLK_STATE s on s.StateId = a.StateID 
join COMMON.CLK_DISTRICTS d on d.DistrictCd = a.DistrictCD 
join common.CLK_DISTRICT_VDC v on v.VdcCd = a.VdcCd where 1 = 1 order by h.ActionDate desc
offset 790 rows 
FETCH first 10 row only

The following is the execution plan for the query.

Execution Plan

How do I optimize this query?

Best Answer

Using OFFSET...FETCH likely introduces a row goal (in the same way that TOP does so - see Setting and Identifying Row Goals in Execution Plans by Paul White for more information on that subject). This is why you see such a difference without that part of the query - the lack of a row goal changes the plan entirely.

The row goal leads to SQL Server choosing the series of nested loops joins in the execution plan you shared. This would normally be fine, but my guess it that the joins are more restrictive than the estimates indicate, and thus it takes a lot longer than expected to get the 10 rows requested.

You can test this theory by adding OPTION (QUERYTRACEON 4138) to the end of the query and running it again.

As far as a proper solution - as Dan Guzman suggested, you could try creating a narrow index on fsams.CTB_SUBSIDY_CARD_HEADER:

CREATE NONCLUSTERED INDEX IX_PaymentLotNo
ON FSAMS.CTB_SUBSIDY_CARD_HEADER (PaymentLotNo)
INCLUDE (OwnerName, TECHID, Id, TotalSubsidy)

Also, don't use SELECT * for your performance-related testing, as it's definitely going to change the final plan that you get (as compared with the column list of the actual query).

There is an implicit conversion going on that could be throwing the query's estimates off - LotNumber is stored as a string in the CTB_SUBSIDY_CARD_HEADER table, which is being converted to an int before being joined to PaymentLotNo in CTB_SUBSIDY_APPLICATION_HISTORY. You should ALTER these columns to have matching data types if possible.

Side note: you're running SQL Server 2014 RTM, which is out of support. You should upgrade to the latest service pack. Also, consider enabling TF 4199 at that time, as the issue you're experiencing could be related to an optimizer bug that has since been corrected.

Related Question