query outside and inside stored procedure
-
I have a query that calculate some count(...) from some table. If I run the query from QA directly it completes in few seconds. If I put exactly the same query inside a stored procedure it takes forever (i.e. one hour or more). Any idea why and what should I try?
Any help will be greatly appreciated.
Gabriela
-
Can you post your procedure please? Include the whole thing if you can.
One quick thing to try though is to insert WITH RECOMPILE to test to see if you were getting a stale plan:
create proc my_proc
(
@param int
)
WITH RECOMPILE
AS
BEGIN
etc
If that doesn't help, you may be seeing problems with parameter sensing.
-
Post the SQL and Procedure.
-
With out having your code, table, data, indexing it is impossible to just say what the problem is, but.
It might possibly be Parameter Sniffing this article may be interesting.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EGAA
-
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create Procedure dbo._RPT_x
@i_FROM datetime,
@i_TO datetime
As
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
declare @TableName varchar(255)
declare @sql varchar(8000)
declare @error varchar(255)
declare @PrevToStart datetime
declare @PrevToEnd datetime
declare @TM_New bigint
select @TM_New = count(distinct a)
from t_a t1 (NOLOCK)
where t1.date between @i_From and @i_To
and t1.b IN (select b from t_b where c = 'T')
and t1.d = 'V'
and NOT EXISTS
(select 1
from t_a t2 (NOLOCK)
where t2.a = t1.a
and t2.date < t1.date
and t2.d = 'V'
)
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Here's another good article to run through on parameter sensing.
http://blogs.msdn.com/khen1234/search.aspx?q=parameter+sniffing+recompile&p=1
Can you see any differences between the estimated query plans of the plain sql and stored proc version?
Did with recompile have any effect?
-
As noted above, try to do a recompile on your procedure.
Here's a little better article/blog about parameter sniffing
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
essentially the query optimizer is trying to guess the values for your @i_from, and @i_to parameters. causing it to create a bad plan.
You may have fragmented indexes, but if its happening now, it will happen in the future.
You can follow the advise in the link above, or just declare an additional from and to parameter
Declare @X_From datetime
, @X_To datetime
set @X_From = @i_From
set @X_To = @i_TO
then the @X_From, and @X_To parameters in your select
Give er a try
-
I think that i have to copy the input parameters in some internal variables, I agree with this. I still need to make some more tests, but it seems that this was the problem. Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply