Stored Proc takes forever but same query runs fast

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc takes forever but same query runs fast

Author

Topic

ecomajor
Starting Member

12 Posts

Posted - 2009-02-25 : 10:21:41

I have the following stored procedure, if I run it as a stored procedure it takes forever to run, however, if I run it as a plain query it returns instantaneously.

If I comment out this line
SELECT @START_DATE = CASE when @START_DATE is null then dateadd(dd,-14,@END_DATE) else @START_DATE end;
it seems to run much faster. Any ideas why a query would perform differently than a stored proc with the same code?

ALTER PROCEDURE [dbo].[Summary_Assignee_Report]
-- Add the parameters for the stored procedure here
@FILTER1 varchar(255),
@FILTER2 varchar(255),
@END_DATE smalldatetime,
@START_DATE smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--SET @END_DATE = '02/14/2009'
--SET @FILTER1 = 'ORACLE'
--SET @FILTER2 = 'CSG'
SELECT @START_DATE = CASE when @START_DATE is null then dateadd(dd,-14,@END_DATE) else @START_DATE end;
-------------------------------------------------------------------

select
1) as statusgroup,
sum(case when substring(cf_statusvalue,0,CHARINDEX ( '-',cf_statusvalue)-1) = 'Resolved'
then 1 end) as Resolved,
sum(case when substring(cf_statusvalue,0,CHARINDEX ( '-',cf_statusvalue)-1) = 'Open'
then 1 end) as Opened,
sum(case when substring(cf_statusvalue,0,CHARINDEX ( '-',cf_statusvalue)-1) = 'Pending'
then 1 end) as Pending,
i.AssignToGroup,
count(*) as created
,CONVERT(char(10),i.CreateDate, 101) as Create_date
,cast(datepart(mm, i.CreateDate) as varchar) as Summary_date,
isnull(AssignToIndividual,'No Assignee') as AssignToIndividual

from HD_CareOne_Incidents i
LEFT OUTER JOIN
RemedyCategoryHierarchy ct ON
i.cf_CategoryInstanceID = ct.category_id
where
i.CreateDate between @START_DATE and @END_DATE
and ((@FILTER1 is null and @FILTER2 is null) or
(ct.catPath like '%' + @FILTER1 + '%' or ct.catPath like '%' + @FILTER1 + '%'))
group by
i.AssignToGroup,

CONVERT(char(10),i.CreateDate, 101),
cast(datepart(mm, i.CreateDate) as varchar),
AssignToIndividual
order by --i.Facility,
i.AssignToGroup--, ct.Category;

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 10:25:12

Because of Parameter sniffing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:26:13

that may be due to parameter sniffing. see below

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources