I am doing some tests using the SQL 2005 profiler.

I have a stored procedure which simply runs one SQL query.

When I run the stored procedure, it takes a long time and performs 800,000 disk reads.

When I run the same query separate to the stored procedure, it does 14,000 disk reads.

I found that if I run the same query with OPTION(recompile), it takes 800,000 disk reads.

From this, I make the (possibly erroneous) assumption that the stored procedure is recompiling each time, and that's causing the problem.

Can anyone shed some light onto this?

I have set ARITHABORT ON. (This solved a similar problem on stackoverflow, but didn't solve mine)

Here is the entire stored procedure:

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 >= @dt_From
  and volume_date < @dt_To
  and last_write_date > @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

Here's a script I run that demonstrates the problem:

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 >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @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 >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @Last_Run_Date
)
THEN 1 else 0 end) OPTION(recompile)