title: Fastest way to fetch a subset (200M) from a very large table (600M) in SQL Server
body: <p>we are facing the following problem and we are trying to come up with the best possible solution.</p>
  
  <p>We are using SQL Server 2008. We have a table that has more than 600 millions records, and has about 25 columns. One of the columns is an ID and is indexed. We need to get a subset of records from this table. There are mainly 2 cases:</p>
  
  <p>a) the subset contains anywhere between 1000 and 200 millions rows ; the IDs of the rows to get are stored in an indexed table in SQL Server;</p>
  
  <p>b) the subset contains less than 1 million rows ; the IDs of the rows to get are stored in the application's server's memory (.NET web app).</p>
  
  <p>Our challenge is to get this subset as fast as possible. We do need this to happen in a few seconds. </p>
  
  <p>Our solutions so far:</p>
  
  <p>a) we do a join between the two tables. That works but that is not fast enough. The query looks something like <code>SELECT * FROM Big_Table JOIN IndexTable ON Big_Table.ID = IndexTable.ID</code>.</p>
  
  <p>b) we do not really have a solution. We tried to run a WHERE IN query but that simply takes forever if the subset approaches the million rows (<code>SELECT * FROM Big_Table WHERE ID IN (ID1, ID2, ID3...)</code>). A solution could be to store the indexes in a temporary table but then this falls back to case a.</p>
  
  <p>We are trying to optimize SQL Server as much as we can, notably using good indexes and partioning. I am more asking today of what you think the best approach is to extract the subset in both cases (a and b).</p>
  
  <p>Any idea or suggestion is appreciated.</p>
  
  <p>Thanks</p>