title: SQL/SAS: Best performance for selecting from big table (2bn rows)
body: <p>I have a non-indexed 2 billion rows table in a read-only SAS SPD server (<strong>bigtable</strong>). I have another 12 million rows table in my workspace (SAS_GRID) with a single column of unique ids (<strong>idlist</strong>). Both tables are updated constantly. I want to filter the bigtable based on idlist, something like:</p>
  
  <pre><code>create table filtered_bigtable as
  select t1.* from bigtable t1 inner join idlist t2
  on t1.id = t2.id;
  </code></pre>
  
  <p>What's the best strategy and code (sql or sas) for doing it quickly?</p>
  
  <p><strong>Edit</strong>: tried Robert's suggestion using a hash table and the query ran in only 40 minutes (10 times faster). Thanks for all the answers!</p>