Hi all,

Currently my application is facing slow performance due to the growth of data. After reading some articles, I have a brief understanding on how pagination works.

However, I would like to ask for some advice on implementing pagination for complex SQL with lots of JOIN and UNION.

For simple SQL, I use the following approach.

-- * Pagination with Simple Query --
SELECT * FROM (
     SELECT field1, field2, field 3, ROW_NUMBER() OVER (ORDER BY field1) AS RowNum      
FROM table1
) AS Result
WHERE Result.RowNum BETWEEN 1 AND 10

For complex SQL

-- * Pagination with Complex Query --
SELECT * FROM (
     SELECT *, ROW_NUMBER() OVER (ORDER BY field1) AS RowNum FROM (
          SELECT field1, field2, field3 FROM table1       
          UNION       
          SELECT field1, field2, field3 FROM table2       
          UNION       
          SELECT field1, field2, field3 FROM table3   
) AS UnionData) AS Result
WHERE Result.RowNum BETWEEN 1 AND 10

Is there a better approach for complex queries? Kindly advise, thanks.