CODEWITHSUNDEEP
sqlsql-serverpagination
Patrik Starck
Patrik Starck

Reputation: 21

SQL pagination fetch next very slow for simple database structure

I'm having trouble regarding pagination for a simple SQL query. I have the following database tables defined.

ord_commission - about 3M rows

ord_commissionord_commission_indexes

ord_order - about 200k rows

ord_order order_order_indexes

Running the following simple SQL query takes about 8 seconds to run for me. However, by removing the last part "offset 0 rows fetch next 50 rows only", the query runs in no time.

How can pagination be achieved with some performance for this type of query?

Are there any indexes I'm missing here, or what can be done?

SELECT 
    oc.commission_no
FROM
    dbo.ord_commission AS oc 
JOIN
    ord_order AS oo ON oo.order_id =oc.order_id
WHERE
    oc.commission_no = '1' OR oo.order_no = '1'
ORDER BY
    oc.commission_no DESC
    OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

I'm also attaching the execution plan for this query:

enter image description here

Upvotes: 0

Views: 514

Answers (1)

Patrik Starck
Patrik Starck

Reputation: 21

The solution was to use 'UNION ALL' instead of the 'or'.

SELECT 
    oc.commission_no
FROM
    dbo.ord_commission AS oc 
JOIN
    ord_order AS oo ON oo.order_id =oc.order_id
WHERE
    oc.commission_no = '1'
    
UNION ALL

SELECT 
    oc.commission_no
FROM
    dbo.ord_commission AS oc 
JOIN
    ord_order AS oo ON oo.order_id =oc.order_id
WHERE
    oo.order_no = '1'



ORDER BY
    oc.commission_no DESC
    OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Upvotes: 1

Related Questions