title: Best strategy for returning large result sets quickly
body: <p>I've been working for the last week on trying to speed up the search results on a site. What we've been working on is moving the paging on the site to SQL. This has sped up the paging drastically, however I still need to query the entire table again to get the total count of the records in that table.</p>
  
  <p>I am caching the totals and only re-running this part of the query when the user alters search parameters to speed up the paging and this works nicely. The issue we are having now is that the load on the CPU of the SQL server is increasing drasticlly and as such the performance of the paging fluctuates drastically (between 100 milliseconds and 2 seconds). </p>
  
  <p>I am just wondering if it wouldn't be better to cache the entire results table on the web server and use either <code>DataTable.Select</code> or a Linq Statement to query the memory table / list? I realise this will add a large memory load to the Web Server, but we are trying to improve speed and as such it may be worth while to upgrade the Web Servers as they are also load balanced, whereas the SQL box is not.</p>