October 6, 2005 at 12:29 pm
Hi
I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.
Thanks.
October 6, 2005 at 12:39 pm
Could you post the text of the proc ?
A.J.
DBA with an attitude
October 6, 2005 at 12:45 pm
Is it possible the queries table(s) have a gazillion rows now, but had zero or few rows when you compiled the procedure? I'm wondering if the execution plan is stuck on stupid, thinking it doesn't need to use an index.
October 6, 2005 at 12:58 pm
Do a search for parameter sniffing, you'll see what to do to fix this problem.
October 6, 2005 at 1:05 pm
Even I tried by executing the SP 'with recompile' Option. Still its taking the same amount of time.
Thanks.
October 6, 2005 at 1:07 pm
Can you post the execution plans?
October 6, 2005 at 1:20 pm
how can copy, paster the execution plan?
October 6, 2005 at 1:23 pm
SET SHOWPLAN_TEXT ON
GO
Select 'query here'
GO
SET SHOWPLAN_TEXT OFF
October 6, 2005 at 1:45 pm
Even with recompile you can suffer the same result from parameter sniffing.
in your procedure reassign in parameters to a new parameter, and use the new parameter in the query.
also dbcc dropcleanbuffers
dbcc freeproccache
Snippit from this link
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EGAA
"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation. Both in SQL Server 2000 and SQL Server 2005, parameter values are sniffed during compilation or recompilation for the following types of batches:
| • | Stored procedures |
| • | Queries submitted via sp_executesql |
| • | Prepared queries |
In SQL Server 2005, the behavior is extended for queries submitted using the OPTION(RECOMPILE) query hint. For such a query (could be SELECT, INSERT, UPDATE, or DELETE), both the parameter values and the current values of local variables are sniffed. The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the OPTION(RECOMPILE) hint. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.
January 26, 2006 at 9:56 am
I had a SP that was taking longer and I found out the optional parameter was doing it. If I supplied the value it went in 30 seconds. If I used a default it took several minutes.
August 13, 2008 at 11:50 am
I had a select with a lot of joins in my sp.
usaly this step took 2 seconds for 200000 records. I wrote the time to a debug table.
With little ram available it could take an hour.
with option recompile at the and of the query in the stored procedure i don't have the problem any longer.
It just goes as fast a in the query analyzer.
select *
from table
option recompile
The rest of my stored procedure is still recompiled.
November 24, 2008 at 2:26 pm
Ditto here. I supplied a value to my optional parm and Wammo! Super fast. Why do optional parameters cause this slow down?
November 24, 2008 at 2:35 pm
Could be that when the optional parameter is missing, the query in the stored procedure is using an inefficient query plan.
November 24, 2008 at 2:53 pm
A little addition here. I was, obviously, having this problem as well. Here is an example of what my proc looked like:
CREATE PROC Reports.MyExampleProc
@StartDate DateTime,
@EndDate DateTime
AS
IF(@EndDate is null) SET @EndDate = getDate()
[DO query here that uses a between statement]
I rewrote this to the following:
CREATE PROC Reports.MyExampleProc
@StartDate DateTime,
@EndDate DateTime
AS
DECLARE @NewEndDate
IF(@EndDate is null) SET @NewEndDate = getDate()
[DO query here that uses a between statement using the @NewEndDate variable instead of @EndDate]
And performance matched what I expected
November 24, 2008 at 2:59 pm
Chris Rogers (11/24/2008)
A little addition here. I was, obviously, having this problem as well. Here is an example of what my proc looked like:CREATE PROC Reports.MyExampleProc
@StartDate DateTime,
@EndDate DateTime
AS
IF(@EndDate is null) SET @EndDate = getDate()
[DO query here that uses a between statement]
I rewrote this to the following:
CREATE PROC Reports.MyExampleProc
@StartDate DateTime,
@EndDate DateTime
AS
DECLARE @NewEndDate
IF(@EndDate is null) SET @NewEndDate = getDate()
[DO query here that uses a between statement using the @NewEndDate variable instead of @EndDate]
And performance matched what I expected
I see the potential for a slight change:
CREATE PROC Reports.MyExampleProc
@StartDate DateTime,
@EndDate DateTime
AS
DECLARE @NewEndDate
--IF(@EndDate is null) SET @NewEndDate = getDate()
SET @NewEndDate = coalesce(@EndDate, getdate()) -- or isnull(@EndDate,getdate())
-- use @NewEndDate in your query instead of EndDate
--[DO query here that uses a between statement using the @NewEndDate variable instead of @EndDate]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply