Query runs fast, but runs slow in stored procedure
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)
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.
As others have mentioned, this could be a 'parameter sniffing' problem. Try including the line:
OPTION (RECOMPILE)
at the end of your SQL query.
There is an article here explaining what parameter sniffing is: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
I guess this is caused by parameter sniffing.
The issue of why a batch takes forever to run inside a SQL stored procedure yet runs instantaneously in SSMS has to do with SQL parameter sniffing, especially with datetime parameters.
There are several excellent articles on parameter sniffing out there.
Here's one of them ( I didn't write it, just passing it on).
http://www.sommarskog.se/query-plan-mysteries.html
On my issue I've run:
exec sp_updatestats
and this speed up my sp from 120s to just 3s. More info about Updating Statistics can be found here https://msdn.microsoft.com/en-us/library/ms173804.aspx
I too got the same problem today. I have dropped and recreated the SP and it worked. This is something with SP cache and when dropped the SP the cached plan has been removed. You can try the same or use 'DBCC FREEPROCCACHE' to delete cache.
精彩评论