Paging and large sql server tables

I've a database two database tables with more than 30 million records, When joining them and use the r开发者_运维知识库ow_number() for paging the query takes a long time and also a lot of resources! How can I trait these tables:

• Using indexed views to save the paging result for each user(views does not support parameters) and if I use a view per user this will be a duplication of the data
• divide tables into smaller tables

Is there any perfect solution?

If you use OPTION FAST(n), it will optimise the query to provide that number of records. I have used this with success before on tables having +20 million records, bringing down the execution time from minutes to milliseconds.

However, if you use this for 10,000th page which happen to be 1,000,000th record, it will not work. If your users want to do something like this, you would have to create an ordered snapshot and perform the queries from that.