title: Why is subquery and join so slow
body: <p>I need to select rows from BUNDLES table which have one of several SAP_STATE_ID values. Those values depends on whether respective SAP status is supposed to be exported or not.</p>
  
  <p>This query runs really fast (there is index on SAP_STATE_ID field) - </p>
  
  <pre><code>SELECT b.* FROM BUNDLES b WHERE b.SAP_STATE_ID IN (2,3,5,6)
  </code></pre>
  
  <p>But... I'd like to fetch list of IDs dynamically, like this:</p>
  
  <pre><code>SELECT b.* FROM BUNDLES b 
  WHERE b.SAP_STATE_ID IN 
  (SELECT s.SAP_STATE_ID FROM SAP_STATES s WHERE s.EXPORT_TO_SAP = 1)
  </code></pre>
  
  <p>And ouch, this query is suddenly taking too much time. I would expect SQL server to run the subquery first (it doesn't depend on anything from main query) and then run whole thing just like in my first example. I tried to rewrite it to use joins instead of subquery:</p>
  
  <pre><code>SELECT b.* FROM BUNDLES b 
  JOIN SAP_STATES s ON (s.SAP_STATE_ID = b.SAP_STATE_ID) 
  WHERE s.EXPORT_TO_SAP = 1
  </code></pre>
  
  <p>but it has same poor performance. It seems like it is running the subquery for each row of BUNDLES table or something like this. I am not very skilled in reading execution plans, but I tried. It says that 81% cost is for scanning Primary key index of BUNDLES (I have no idea why it should do such a thing, there is BUNDLE_ID field defined as PRIMARY KEY, but it doesn't appear in the query at all...)</p>
  
  <p>Does anyone have an explanation why is SQL server so "stupid"? Is there a way to achieve what I want with good performance but without the need to provide static list of SAP_STATE_IDs?</p>
  
  <p>script for both tables and relevant indexes - <a href="http://mab.to/xbYiI0wKj" rel="nofollow">http://mab.to/xbYiI0wKj</a></p>
  
  <p>execution plan for subquery version -  <a href="http://mab.to/8Qh6gpdYZ" rel="nofollow">http://mab.to/8Qh6gpdYZ</a></p>
  
  <p>query plan for version with joins - <a href="http://mab.to/YCqeGCUbr" rel="nofollow">http://mab.to/YCqeGCUbr</a></p>
  
  <p>(for some reason these two plans looks the same and both suggest creating BUNDLES.SAP_STATE_ID index, which is already there)</p>