title: Need suggestions to further optimize this SQL Server query
body: <p>I wrote this very simple query and it has been running for over 16 hours now. I have tried all ways to optimize it but have no clue on how can it be improved further. Please help.</p>
  
  <pre><code>select 
      a.*, b.TM_IN_XIT 
  into
      scratch.dbo.ab_peak2 
  from 
      scratch.dbo.ab_peak1 a 
  left join 
      Scratch.dbo.ab_tnt b on a.DC_ZIP between b.ORIG_ZIP_low 
                           and b.ORIG_ZIP_high 
                           and a.Destination between b.DEST_ZIP_low and b.DEST_ZIP_high  
                           and a.Carr_Mode = b.Mode;
  </code></pre>
  
  <p>Table <code>scratch.dbo.ab_peak1</code> looks as below and has ~700k records:</p>
  
  <pre><code>+-----------------------------------------------+
  | ShipmentNumber  DC_ZIP  Destination Carr_Mode |
  +-----------------------------------------------+
  | 252838748       60622       10016      A      |
  | 252731857       60622       40517      A      |
  | 252685087       60622       91601      B      |
  | 252574905       60622       7017       B      |
  | 252877256       60622       97230      A      |
  | 254791362       20166       54971      B      |
  | 255866277       60622       19131      A      |
  | 255728088       60622       27713      B      |
  | 255614555       60622       10009      A      |
  | 255823071       60622       33556      B      |
  +-----------------------------------------------+
  </code></pre>
  
  <p>Table <code>Scratch.dbo.ab_tnt</code> looks as below and has ~1.5M records:</p>
  
  <pre><code>+-----------------------------------------------------------------------------------+
  | Mode    ORIG_ZIP_low    ORIG_ZIP_high   DEST_ZIP_low    DEST_ZIP_high   TM_IN_XIT |
  +-----------------------------------------------------------------------------------+
  |   A        41042            41042          62556            62556          2      |
  |   B        41042            41042          62556            62556          3      |
  |   A        41042            41042          62557            62557          1      |
  |   B        41042            41042          62557            62557          2      |
  |   A        41042            41042          62558            62563          2      |
  |   B        41042            41042          62558            62563          3      |
  |   A        41042            41042          62565            62567          1      |
  |   B        41042            41042          62565            62567          2      |
  |   A        41042            41042          62568            62570          2      |
  |   B        41042            41042          62568            62570          3      |
  +-----------------------------------------------------------------------------------+
  </code></pre>
  
  <p>What I'm trying to achieve - "a" is a Shipments table and "b" is a table that has transit times for all origin-destination combos. The structure of "b" table is based on zip ranges as shown above. I'm trying to bring in transit time for each shipment of "a" table by looking up in "b" table.</p>
  
  <p>I have tried following already:</p>
  
  <ol>
  <li>Retained zipcodes in TNT table >= min(zipcodes in PEAK1 table) and &lt;= max(zipcodes in PEAK1 table)</li>
  <li>Created indices on all columns of TNT table.</li>
  </ol>
  
  <p>Any other suggestions?</p>