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...

Silaphet,

Below is my code,

DECLARE @Modevarchar(10),
@UserIDvarchar(36),
@FromDatesmalldatetime,
@ToDatesmalldatetime,
@Insttinyint,
@LocationIDsmallint,
@BunitIDtinyint,
@TeamIDint

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