title: T-SQL Select where "IN" very bad Performance impact
body: <p>when use the following code:</p>
  
  <pre><code>SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;
  
  SELECT distinct
  
          Id,
          StoreName,
          ......,
          ......,
  
      from INV_Transactions
          inner join ....
          left outer join ....
  
      WHERE StoreId in (select Id from #StoreIdsPermision)
  </code></pre>
  
  <p>Execution time is 4 minutes.</p>
  
  <p>and when use the same code with comma syntax in "WHERE IN" statement, the execution time is 3 seconds, why? and how fix that?</p>
  
  <pre><code>SELECT distinct
  
          Id,
          StoreName,
          ......,
          ......,
  
      from INV_Transactions
          inner join ....
          left outer join ....
  
      WHERE StoreId in (4,7,9,15,22,........)
  </code></pre>
  
  <p>NOTE: the Execution time for the following statement less than 1 second, and #StoreIdsPermision about 140 row only</p>
  
  <pre><code>SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;
  </code></pre>
  
  <p>Actual Execution Plan for Part 1
  <img src="https://i.imgur.com/hTnKnHx.png" alt="Actual Execution Plan"></p>
  
  <p>Actual Execution Plan for Part 2
  <img src="https://i.imgur.com/MXBgc29.png" alt="Actual Execution Plan"></p>
  
  <p>Actual Execution Plan when use comma syntax
  <img src="https://i.imgur.com/hbBv63i.png" alt="Actual Execution Plan"></p>
  
  <p>UPDATE 1: </p>
  
  <p>VERY GOOD Suggestion from "Eponyme Web" Suggestion Number Two, when added SET FORCEPLAN ON In the First and SET FORCEPLAN OFF in The last Query, the Execution Time is Normal, what Happen?</p>
  
  <p>UPDATE 2:</p>
  
  <p>I replaced all inner join to left outer join and worked fine also without FORCEPLAN ON</p>