title: 1 billion rows table filtering and join
body: <p>So here is the deal, I have two tables, each has ~ 1B rows. I am trying to query the tables so I can process the data and insert them into other tables. But I don't need to process all ~1B. I only need 10s or 100s of rows to work with each time.</p>
  
  <p>However, the join and the filtering take a long time to return the data. </p>
  
  <p>The tables don't have non-clustered indexes on them but they do have a clustered index on the primary key.</p>
  
  <p>An example of the query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select Col1, Col2, Col3 
  from 1b_table_1 t1
  inner join (
    select * 
    from 1b_table_2 
    where expression=condition
  ) t2
  on t1.join_col = t2.join_col
  where CAST(t2.timestamp as date) &gt;= date_var1
  and CAST(t2.timestamp as date) &lt;= date_var2
  </code></pre>
  
  <p><strong>UPDATE</strong>
  I tried adding a non-clustered index on 1b_table_1 but the issue I have now is that there is a script running somewhere else that is continuously inserting data into these two tables and I can't create a new index or it will lock the table when building the index and the data write will begin to fail and will cause data loss.</p>
  
  <p><strong>ANOTHER ONE</strong></p>
  
  <pre><code>SELECT count(*) from 1b_table_1
  </code></pre>
  
  <blockquote>
    <p>~1.2B</p>
  </blockquote>
  
  <pre><code>SELECT count(*) from 1b_table_2
  </code></pre>
  
  <blockquote>
    <p>~22M</p>
  </blockquote>
  
  <pre><code>SELECT count(*) from 1_table_2 where col like condition_string
  </code></pre>
  
  <blockquote>
    <p>BEEN RUNNING FOR OVER 5 minutes and no results.
    The column here is nvarchar(max)!!</p>
  </blockquote>
  
  <p>Also, I can't change the table structures or indexes on the table.</p>