Thanks for all your responses.
What we noticed is that it was taking 6 mins just to number the rows, so each call to the SP would take 6 mins numbering + everything else ( selects from other views)
We cut this down by creating a global temp table using the referenceID and the rownumber() in the SP. The first time the SP is hit it creates this global table. Sequential 'paginated' hits would use the global temp table. We don't need to order the data so we didn't use ordering in the code which cut the time from 6 mins to 3 mins. We had to ensure this global table was deleted at the end of the process of uploading all data or in rollback.
We also used clustered index.
The reason why we're uploading everyday is because of the complexity of the database and the app that checks for updates. We use checksums