title: Stange query peformance: fast from SSMS, slow from app (asp.net)
body: <p>Well, I know. <strong>'fast from SSMS, slow from app'</strong> - that sounds very familiar for someone.
  One can start thinking about parameter sniffing or connection settings. But I guess that's not the case for me.</p>
  
  <p>So that's the query:</p>
  
  <pre><code>SELECT [ST].*, [STL].*
  FROM [WP_CashCenter_StockTransaction] AS [ST] 
      LEFT JOIN [WP_CashCenter_StockTransactionLine] AS [STL] ON ([STL] [StockTransaction_id] = [ST].[id]) 
  WHERE 
  ([ST].[Type] IN (0, 1, 10, 9)
  AND ([STL].[Direction] IN (0, 1) OR [STL].[id] IS NULL) 
  AND [ST].[Status] IN (0,1)
  AND ( ([STL].[StockContainer_id] = 300000742600 OR [STL].[id] IS NULL) AND [ST].[StockContainerID] = 300000742600))
  </code></pre>
  
  <p>I'll post links to images of execution plans, if you don't mind (pls tell in comments), cause there's gonna be many of them. </p>
  
  <p>Execution plan that I get from SSMS: <a href="http://i.imgur.com/DjTypV2.png" rel="nofollow">http://i.imgur.com/DjTypV2.png</a> (runs fraction of a sec)</p>
  
  <p>Execution plan, that's used for query when it's executed from app:  <a href="http://i.imgur.com/Ra45CAo.png" rel="nofollow">http://i.imgur.com/Ra45CAo.png</a> (runs ~3sec)</p>
  
  <p>So, for some reason, sql-server makes wrong estimations and prefers a table scan in second case. </p>
  
  <p>The query is built dynamically and new plan is generated for each new value of StockContainerID (no parameters).</p>
  
  <p>Well, okay, so gave up trying to figure out the problem and just used <strong>FORCESEEK</strong> hint:</p>
  
  <pre><code>SELECT [ST].*, [STL].*
  FROM [WP_CashCenter_StockTransaction] AS [ST] WITH(FORCESEEK)
      LEFT JOIN [WP_CashCenter_StockTransactionLine] AS [STL] WITH(FORCESEEK) ON ([STL].[StockTransaction_id] = [ST].[id]) 
  </code></pre>
  
  <p>Now, the execution plans seem to be identical:</p>
  
  <p><a href="http://i.imgur.com/orq7Pmx.png" rel="nofollow">http://i.imgur.com/orq7Pmx.png</a> (executed from app). But it still takes ~3secs.</p>
  
  <p>Take a look at this:</p>
  
  <p><a href="http://i.imgur.com/ZFhyWYc.png" rel="nofollow">http://i.imgur.com/ZFhyWYc.png</a> (SSMS, 1 number of executions, 1 estimated rows)</p>
  
  <p><a href="http://i.imgur.com/bIeTE13.png" rel="nofollow">http://i.imgur.com/bIeTE13.png</a> (app, 1 numer of executions, 655k estimated rows)</p>
  
  <p><a href="http://i.imgur.com/FERBNQR.png" rel="nofollow">http://i.imgur.com/FERBNQR.png</a> (SSMS, 1 numebr of executions, 1 estimated rows)</p>
  
  <p><a href="http://i.imgur.com/pm2k8CS.png" rel="nofollow">http://i.imgur.com/pm2k8CS.png</a> (app, 655k number of executions, 1 estimated rows)</p>
  
  <p>You should have noticed that the second plan uses parallelism. I don't know if that can be a reason for the problem (I think no).</p>