title: How to cache a big table from SQL Server
body: <p>I have a table with a lot of rows (3 million) from which I need to query some rows at several points in my app. The way I found to do this is querying all the data the first time that any was needed and storing it in a <code>static DataTable</code> with <code>SqlAdapter.Fill()</code> for the rest of the app life.</p>
  
  <p>That's fast, because then when I need something I use <code>DataTable.Select("some query")</code> and the app processes the info just nice.</p>
  
  <p>The problem is that this table takes about 800MB of RAM, and I have to run this app in PCs where it might be too much.</p>
  
  <p>The other way I thought was to query the data I need each time. This takes little memory but has poor performance (a lot of queries to the database, which is at a network address and with 1000 queries you start to notice the ping and all that..).</p>
  
  <p>Is there any intermediate point between performance and memory usage?</p>
  
  <hr>
  
  <p>EDIT: What I'm retrieving are sales, which have a date, a product and a quantity. I query by product, and it isn't indexed that way. But anyways, making 1000 queries, even if the query took 0.05s, a 0.2s ping makes a total of 200 seconds...</p>