Adhoc vs PROC

Hi Guru,

When I ran my adhoc script below it generated only 45000 reads or 4 seconds but when I wrapped it into procedure it took about two minutes or millions of reads. The parameters calling both adhoc and proc are indeed the same. I'm pretty 99.9% sure that the proc does not recompile because I don't mix up between DDL and DML, no temp tables or any thing to cause proc to recompile. The big difference is adhoc used index scan for 45% but proc used bookmark lookup for 75%. Why it's so difference since they both returned the same results?

Please help...

Below is my code,

DECLARE @.Mode varchar(10),
@.UserID varchar(36),
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.Inst tinyint,
@.LocationID smallint,
@.BunitID tinyint,
@.TeamID int

SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805

--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END

IF @.Mode = 'RM'
BEGIN
IF @.BUnitId > 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
END

When wrapped in SP have you recompiled for the first time to execute the same, check the estimated execution plan from QA in this case that explains why there is delay.