sql server - Query runs fast, but runs slow in stored procedure

Question

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)

Answer 1

OK, we have had similar issues like this before.

The way we fixed this, was by making local parameters inside the SP, such that

DECLARE @LOCAL_Contract_ID int, 
        @LOCAL_dt_From smalldatetime, 
        @LOCAL_dt_To smalldatetime, 
        @LOCAL_Last_Run_Date datetime

SELECT  @LOCAL_Contract_ID = @Contract_ID, 
        @LOCAL_dt_From = @dt_From, 
        @LOCAL_dt_To = @dt_To, 
        @LOCAL_Last_Run_Date = @Last_Run_Date

We then use the local parameters inside the SP rather than the parameters that was passed in.

This typically fixed the issue for Us.

We believe this to be due to parameter sniffing, but do not have any proof, sorry... X-)

EDIT:

Have a look at Different Approaches to Correct SQL Server Parameter Sniffing for some insightful examples, explanations and fixes.