title: Query runs fast, but runs slow in stored procedure
body: <p>I am doing some tests using the SQL 2005 profiler.</p>
  
  <p>I have a stored procedure which simply runs one SQL query.</p>
  
  <p>When I run the stored procedure, it takes a long time and performs 800,000 disk reads.</p>
  
  <p>When I run the same query separate to the stored procedure, it does 14,000 disk reads.</p>
  
  <p>I found that if I run the same query with OPTION(recompile), it takes 800,000 disk reads.</p>
  
  <p>From this, I make the (possibly erroneous) assumption that the stored procedure is recompiling each time, and that's causing the problem.</p>
  
  <p>Can anyone shed some light onto this? </p>
  
  <p>I have set ARITHABORT ON. (This solved a similar problem on stackoverflow, but didn't solve mine)</p>
  
  <p>Here is the entire stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED]
   @Contract_ID int,
   @dt_From smalldatetime,
   @dt_To smalldatetime,
   @Last_Run_Date datetime
  AS
  BEGIN
   DECLARE @rv int
  
  
   SELECT @rv = (CASE WHEN EXISTS
   (
    select * from 
    view_contract_version_last_volume_update
    inner join contract_version
    on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
    where contract_version.contract_id=@Contract_ID
    and volume_date &gt;= @dt_From
    and volume_date &lt; @dt_To
    and last_write_date &gt; @Last_Run_Date
   )
   THEN 1 else 0 end)
  
   -- Note that we are RETURNING a value rather than SELECTING it.
   -- This means we can invoke this function from other stored procedures
   return @rv
  END
  </code></pre>
  
  <p>Here's a script I run that demonstrates the problem:</p>
  
  <pre><code>DECLARE 
   @Contract_ID INT,
   @dt_From smalldatetime,
   @dt_To smalldatetime,
   @Last_Run_Date datetime,
      @rv int
  
  
  SET @Contract_ID=38
  SET @dt_From='2010-09-01'
  SET @dt_To='2010-10-01'
  SET @Last_Run_Date='2010-10-08 10:59:59:070'
  
  
  -- This takes over fifteen seconds
  exec GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED @Contract_ID=@Contract_ID,@dt_From=@dt_From,@dt_To=@dt_To,@Last_Run_Date=@Last_Run_Date
  
  -- This takes less than one second!
  SELECT @rv = (CASE WHEN EXISTS
  (
   select * from 
   view_contract_version_last_volume_update
   inner join contract_version
   on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
   where contract_version.contract_id=@Contract_ID
   and volume_date &gt;= @dt_From
   and volume_date &lt; @dt_To
   and last_write_date &gt; @Last_Run_Date
  )
  THEN 1 else 0 end)
  
  
  -- With recompile option. Takes 15 seconds again!
  SELECT @rv = (CASE WHEN EXISTS
  (
   select * from 
   view_contract_version_last_volume_update
   inner join contract_version
   on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
   where contract_version.contract_id=@Contract_ID
   and volume_date &gt;= @dt_From
   and volume_date &lt; @dt_To
   and last_write_date &gt; @Last_Run_Date
  )
  THEN 1 else 0 end) OPTION(recompile)
  </code></pre>