I've come across a weird situation. I have a stored procedure (code below), and it's fairly simple. I frequently "unroll" stored procedures to work on the execution plan. So I unrolled this one and found a huge discrepancy in the execution times between the SP and the straight select.

The SP executes in 1:14 and the straight SQL executes in :12!

Maybe I need another pair of eyeballs. Can you see any difference between the two?

-- This takes 1:14

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/* ******************************************************************
   Stored Procedure usp_DispositionSummaryByTeamAnalyst_sel
   ******************************************************************
 
 Original Author: Keith Payne
   Creation Date: 05/09/2004 
      Work Order: 299057
 
*********************************************************************
Purpose: 
        Select calls inbound and calls disposed from the Genesys system.
	Total the calls by Customer Service Center team.  Subtotal by individual analyst
	Results are filtered by the call date.
	NOTE: A selection of Team or Analyst is required.  The source
	data is not normalized and will yield incorrect aggregates if
	the report is run for all teams and analysts.
            
Revision History: 
	05/18/2005 Removed the call date
	09/28/2005 Rewrote to include all Analysts within the team.
		Default for @TeamID is 158 - All Agents
 
*********************************************************************
 
How to Use:   
 Login as dbo or equivalent in Query Analyzer. 
 To select by team:
	EXEC usp_DispositionSummaryByTeamAnalyst_sel '2005-01-01', '2005-01-28', 216, NULL
 To select by analyst:
	EXEC usp_DispositionSummaryByTeamAnalyst_sel '20050901', '20050921', NULL, 409
	
*/
---------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[usp_DispositionSummaryByTeamAnalyst_sel]
	(
		@FromDate datetime,
		@ToDate datetime,
		@TeamID int = 158,
		@AnalystID int = NULL
	)
AS

IF NOT (@FromDate IS NOT NULL AND @ToDate IS NOT NULL AND @ToDate >= @FromDate)
	BEGIN
		RAISERROR('Parameters @FromDate and @ToDate are required.  @FromDate must be <= @ToDate;usp_DispositionSummaryByTeamAnalyst_sel',16,1)
		RETURN
	END

IF @TeamID IS NULL
	set @TeamID = 158

declare @Days int
set @Days = DATEDIFF(dd,@FromDate, @ToDate)

declare @Sequence table ([id] int)

insert into @Sequence
	select [id] from [BA_Shared].dbo.udf_SequenceTable(@Days)

declare @Detail table (
	[TeamID] [numeric](10, 0) NULL,
	[TeamName] [varchar](240) NULL,
	[AnalystID] [numeric](10, 0) NULL,
	[AnalystName] [varchar](240) NULL,
	[LoginCode] [int] NULL,
	[CallDate] [varchar](30) NULL,
	[CallTime] [varchar](30) NULL,
	[AccountNumber] [varchar](16) NULL,
	[AccountName] [varchar](50) NULL,
	[CallerName] [varchar](50) NULL,
	[CallReason] [varchar](50) NULL,
	[ReasonCode] [int] NULL,
	[CallDisposition] [char](50) NULL,
	[DispositionCode] [int] NULL,
	[IsCallResolved] [char](1) NOT NULL
)

insert into @Detail
	select * from dbo.udf_DispositionCodeDetail (@FromDate, @ToDate, @TeamID, @AnalystID)

select
	t.TeamID,
	t.TeamName,
	t.AnalystID,
	t.AnalystName,
	d.CallDate,
	c.CallsReceived,
	SUM(CASE d.IsCallResolved WHEN 'Y' THEN 1 ELSE 0 END) as CallsResolved
from
	dbo.vw_Genesys_TeamAnalystLogin t
	full join @Sequence s
		left join @Detail d
			inner join [dbo].[vw_Genesys_AgentCallStats] c
				on c.AnalystID = d.AnalystID
					and c.TeamID = d.TeamID
					and c.CallDate = d.CallDate
			on d.CallDate = DATEADD(dd,s.[id],@FromDate)
		on t.TeamID = d.TeamID
			and t.AnalystID = d.AnalystID
where
	COALESCE(t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived) IS NOT NULL
	AND t.TeamID = @TeamID
	AND ((d.AnalystID = @AnalystID)
		OR d.AnalystID IS NULL AND @AnalystID IS NULL)

group by
	t.TeamID,
	t.TeamName,
	t.AnalystID,
	t.AnalystName,
	d.CallDate,
	c.CallsReceived
order by
	t.AnalystName,
	d.CallDate

-- This takes :12

declare	@FromDate datetime,
		@ToDate datetime,
		@TeamID int,
		@AnalystID int

set @FromDate = '20050901'
set @ToDate = '20050921'
set @TeamID = NULL
set @AnalystID = 409

IF NOT (@FromDate IS NOT NULL AND @ToDate IS NOT NULL AND @ToDate >= @FromDate)
	BEGIN
		RAISERROR('Parameters @FromDate and @ToDate are required.  @FromDate must be <= @ToDate;usp_DispositionSummaryByTeamAnalyst_sel',16,1)
		RETURN
	END

IF @TeamID IS NULL
	set @TeamID = 158

declare @Days int
set @Days = DATEDIFF(dd,@FromDate, @ToDate)

declare @Sequence table ([id] int)

insert into @Sequence
	select [id] from [BA_Shared].dbo.udf_SequenceTable(@Days)

declare @Detail table (
	[TeamID] [numeric](10, 0) NULL,
	[TeamName] [varchar](240) NULL,
	[AnalystID] [numeric](10, 0) NULL,
	[AnalystName] [varchar](240) NULL,
	[LoginCode] [int] NULL,
	[CallDate] [varchar](30) NULL,
	[CallTime] [varchar](30) NULL,
	[AccountNumber] [varchar](16) NULL,
	[AccountName] [varchar](50) NULL,
	[CallerName] [varchar](50) NULL,
	[CallReason] [varchar](50) NULL,
	[ReasonCode] [int] NULL,
	[CallDisposition] [char](50) NULL,
	[DispositionCode] [int] NULL,
	[IsCallResolved] [char](1) NOT NULL
)

insert into @Detail
	select * from dbo.udf_DispositionCodeDetail (@FromDate, @ToDate, @TeamID, @AnalystID)

select
	t.TeamID,
	t.TeamName,
	t.AnalystID,
	t.AnalystName,
	d.CallDate,
	c.CallsReceived,
	SUM(CASE d.IsCallResolved WHEN 'Y' THEN 1 ELSE 0 END) as CallsResolved
from
	dbo.vw_Genesys_TeamAnalystLogin t
	full join @Sequence s
		left join @Detail d
			inner join [dbo].[vw_Genesys_AgentCallStats] c
				on c.AnalystID = d.AnalystID
					and c.TeamID = d.TeamID
					and c.CallDate = d.CallDate
			on d.CallDate = DATEADD(dd,s.[id],@FromDate)
		on t.TeamID = d.TeamID
			and t.AnalystID = d.AnalystID
where
	COALESCE(t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived) IS NOT NULL
	AND t.TeamID = @TeamID
	AND ((d.AnalystID = @AnalystID)
		OR d.AnalystID IS NULL AND @AnalystID IS NULL)

group by
	t.TeamID,
	t.TeamName,
	t.AnalystID,
	t.AnalystName,
	d.CallDate,
	c.CallsReceived
order by
	t.AnalystName,
	d.CallDate