BTW, this is a link to this question on another forum (just to see all the ideas discussed)

http://forums.asp.net/t/1436879.aspx

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