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.

Adding Hours, Minutes, Seconds (SQL 2000)

Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...

declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'

select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)

now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)

sum1
----
47:10:15

Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5

JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))

insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')

select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx