I'm not 100% certain, but I think using UNION it always first tries to produce this huge result set and only then apply Row_NUMBER(), so no wonder it would be slow.

You may try to create the inner result (the UNION one) as a view and see if the performance could be improved.

>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.