Why is OFFSET / FETCH causing Performance issues with Queries

52 Views Asked by At

My SQL query is taking too much time to complete execution while using OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

It will complete execution in 0 seconds if I

  1. Remove the FETCH NEXT 20 ROWS ONLY and use only OFFSET 20 ROWS

  2. Insert the data into a temp table first and then use OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

I want to know why this happening

I have this select query in T-SQL

SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME
    , ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE
    , ITEM_CAT.NAME CATEGORY_NAME
FROM B1.SP_ITEM_MF ITEM
INNER JOIN B1.SP_ADMIN_STRUCTURE ITEM_CAT ON ITEM.CODE_CATEGORY_ID = ITEM_CAT.ID
    AND ITEM_CAT.RECORD_TYPE ='CD'
    AND ITEM_CAT.ACTIVE='Y'
INNER JOIN [A1].SP_REQ_CATEGORY_MAPPING MAP ON MAP.ITEM_CATEGORY_ID = ITEM.CODE_CATEGORY_ID
INNER JOIN B1.SP_ADMIN_STRUCTURE  REQ_CAT ON REQ_CAT.ID = MAP.REQ_CATEGORY_ID
    AND REQ_CAT.RECORD_TYPE = 'RQ'
    AND REQ_CAT.ACTIVE = 'Y'
    AND REQ_CAT.CODE IN ('CRW','SCHF')
ORDER BY ITEM.CODE
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

Which is returning 20 records and taking more than 5 minutes to complete execution.

When I remove the FETCH NEXT 20 ROWS ONLY it returns 2000 records and complete execution in just 0 seconds.

Also if I rewrite the query to insert the result set into a temp table (added below) and then use OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY on that result set that also complete execution in 0 seconds .

CREATE TABLE #TempPagination (
    RowNum INT,
    ITEM_ID BIGINT,
    ITEM_CODE VARCHAR(64),
    ITEM_NAME VARCHAR(256),
    CATEGORY_ID INT,
    CATEGORY_CODE VARCHAR(8),
    CATEGORY_NAME VARCHAR(64)
);

INSERT INTO #TempPagination (ITEM_ID, ITEM_CODE, ITEM_NAME, CATEGORY_ID, CATEGORY_CODE, CATEGORY_NAME)
SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME
    , ITEM.CODE_CATEGORY_ID CATEGORY_ID,ITEM_CAT.CODE CATEGORY_CODE,ITEM_CAT.NAME CATEGORY_NAME
FROM B1.SP_ITEM_MF ITEM
INNER JOIN B1.SP_ADMIN_STRUCTURE ITEM_CAT ON ITEM.CODE_CATEGORY_ID = ITEM_CAT.ID
    AND ITEM_CAT.RECORD_TYPE = 'CD'
    AND ITEM_CAT.ACTIVE = 'Y'
INNER JOIN A1.SP_REQ_CATEGORY_MAPPING MAP ON MAP.ITEM_CATEGORY_ID = ITEM.CODE_CATEGORY_ID
INNER JOIN B1.SP_ADMIN_STRUCTURE REQ_CAT ON REQ_CAT.ID = MAP.REQ_CATEGORY_ID
    AND REQ_CAT.RECORD_TYPE = 'RQ'
    AND REQ_CAT.ACTIVE = 'Y'
    AND REQ_CAT.CODE IN ('CRW','SCHF');

SELECT 
    ITEM_ID,
    ITEM_CODE,
    ITEM_NAME,
    CATEGORY_ID,
    CATEGORY_CODE,
    CATEGORY_NAME
FROM #TempPagination
ORDER BY ITEM_CODE
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

DROP TABLE #TempPagination;




Can someone explain why this happening? I have indexes on the columns and the version I am using is 2019.

  1. Why is the query taking more time without the temp table, and not taking time when inserted into temp table first?

  2. Why is FETCH NEXT 20 ROWS ONLY causing too much performance degradation?

Plan with temp table: https://brentozar.com/pastetheplan/?id=H1_VmgyAa

Plan without temp table: https://brentozar.com/pastetheplan/?id=ByLhQeJAp

0

There are 0 best solutions below