title: Weird SQL Server query problem
body: <p>So I have this weird problem with an SQL Server stored procedure. Basically I have this long and complex procedure. Something like this:</p>
  
  <pre><code>SELECT Table1.col1, Table2.col2, col3
  FROM Table1 INNER JOIN Table2
       Table2 INNER JOIN Table3
       -----------------------
       -----------------------
       (Lots more joins)
  WHERE Table1.Col1 = dbo.fnGetSomeID() AND (More checks)
       -----------------------
       -----------------------
  (6-7 More queries like this with the same check)
  </code></pre>
  
  <p>The problem is that check in the WHERE clause at the end <b>Table1.Col1 = dbo.fnGetSomeID()</b>. The function dbo.fnGetSomeID() returns a simple integer value <b>1</b>. So when I hardcode the value <b>1</b> where the function call should be the SP takes only about 15 seconds. BUT when I replace it with that function call in the WHERE clause it takes around 3.5 minutes. </p>
  
  <p>So I do this:</p>
  
  <pre><code>DECLARE @SomeValue INT
  SET @SomeValue = dbo.fnGetSomeID()
  --Where clause changed
  WHERE Table1.Col1 = @SomeValue
  </code></pre>
  
  <p>So now the function is only called once. But still the same 3.5 minutes. So I go ahead and do this:</p>
  
  <pre><code>DECLARE @SomeValue INT
  --Removed the function, replaced it with 1
  SET @SomeValue = 1
  --Where clause changed
  WHERE Table1.Col1 = @SomeValue
  </code></pre>
  
  <p>And still it takes 3.5 minutes. Why the performance impact? And how to make it go away?</p>