See the order here:

Re: Interview questions Thread #1380550 Message #1401825


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