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.

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
ENDOn 4 Jan 2007 07:12:58 -0800, kmounkhaty@.yahoo.com wrote:

Quote:

Originally Posted by

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

Hi Silaphet,

You might suffer from parameter sniffing. Google for this term to find
out what it is and how you can try to deal with it.

You should also consider creating three procedures for the three
versions of the SELECT statement, and change your current stored proc
into a simple IF ELSE tree to call either one of them. This way, each
stored proc can get an execution plan that is otimized for its
parameters.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

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?

Run this:

select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')

If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.

It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First off, I think I see a bug:

"(StartDate BETWEEN @.FromDate-365 AND @.ToDate-365)"

What happens on leap years?

It's impossible to tell what the performance of this query is going to
be like without knowing how all of the tables and views are structured.
I'd imagine that your biggest issue is going to be with the range of
dates that you're dealing with, but there's no way to know for sure
without knowing the table structure, view definitions, and statistical
distribution of data in the tables.

My advice: If it's not a proc that's being used a lot during the day,
try defining it with the RECOMPILE option (see docs) so that the
procedure gets re-optimized each time you run it.

-Dave

Erland Sommarskog wrote:

Quote:

Originally Posted by

kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

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?

>
Run this:
>
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
>
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

Adding Users to a Role in Bulk

I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.

Here is the proc
CREATE proc sp_MyProc
@.DBName varchar(256)
As

Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name

Open UID_Cursor
Declare @.Name as varchar(256)
Declare @.TempString as varchar(8000)

Fetch Next from UID_Cursor into @.Name

While (@.@.Fetch_Status <> -1)
Begin

Set @.TempString = 'sp_addrolemember ''db_datareader'', ''' + @.Name + ''''
Exec(@.TempString)

Fetch Next from UID_Cursor Into @.Name
End

Close UID_Cursor
Deallocate UID_CursorYou may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.|||Originally posted by MCrowley
You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.

It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.

My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .|||OK. I found the issue, which leads to another question.

I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.

My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.

This leads to another issue:
How do I let sp_addrolemember know which database I want it to affect?|||have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):

exec master.dbo.sp_configure 'allow', 1
reconfigure with override
go
declare @.roluid smallint, @.ruidbyte smallint, @.ruidbit smallint
select @.roluid = uid from sysusers
where name = 'db_datawriter' and issqlrole = 1

select @.ruidbyte = ((@.roluid - 16384) / 8) + 1
, @.ruidbit = power(2, @.roluid & 7)
-- update u set roles = convert(varbinary(2048),
select roles = convert(varbinary(2048),
substring(convert(binary(2048), roles), 1, (((@.roluid - 16384) / 8) + 1)-1)
+ convert(binary(1), (@.ruidbit) | substring(convert(binary(2048), roles), @.ruidbyte, 1))
+ substring(convert(binary(2048), roles), @.ruidbyte+1, 2048-@.ruidbyte) ),
updatedate = getdate()
from sysusers u inner join master.dbo.sysxlogins x
on u.sid=x.sid
go
exec master.dbo.sp_configure 'allow', 0
reconfigure with override
go

Adding optional criteria in a select proc

I would like to write 1 proc that can take additional criteria if its sent in. An example is:

select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = @.VENDOR
and Sitecode = @.SITECODE
and PackageType = @.PACKAGETYPE
)HB on HA.VendorPackageId = HB.VendorPackageId
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)

Multiple @.COMPONENTTYPE, @.VALIDITEMTYPE,@.ITEMVALUE can be sent in.
Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:

and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3
)

Ignoring the 2nd 2 selects if @.COMPONENTTYPE_2, @.VALIDITEMTYPE_2,@.ITEMVALUE_2 and @.COMPONENTTYPE_3, @.VALIDITEMTYPE_3,@.ITEMVALUE_3 are = ''

Thanks for your help in advance.Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).

I've never found a satisfactory generic way to handle this kind of problem.

-PatP|||Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).

I've never found a satisfactory generic way to handle this kind of problem.

-PatP

You will never have a 2nd and not a first.

You'll either have 1, 2 or 3 of
@.COMPONENTTYPE
@.VALIDITEMTYPE
@.ITEMVALUE

if they send in all 3 @.ITEMVALUE_1 thourgh 3 will be populated and so will @.VALIDITEMTYPE & @.ITEMVALUE|||Is this close to what you want? I hope you are not expecting any conditional search such as this to run particularly fast...

select Distinct Criteria.PriceId, Criteria.VendorPackageId
from Criteria
Inner Join ValidVendorPackages
on Criteria.VendorPackageId = ValidVendorPackages.VendorPackageId
and ValidVendorPackages.Vendor = @.VENDOR
and ValidVendorPackages.Sitecode = @.SITECODE
and ValidVendorPackages.PackageType = @.PACKAGETYPE
Left Outer Join ValidItemCriteria VIC_1
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_1.Destination = @.DESTINATION
and VIC_1.LengthOfStay = @.LENGTHOFSTAY
and VIC_1.Ages = @.AGE
and VIC_1.ComponentType = @.COMPONENTTYPE_1
and VIC_1.ValidItemType = @.VALIDITEMTYPE_1
and VIC_1.ItemValue = @.ITEMVALUE_1
Left Outer Join ValidItemCriteria VIC_2
on Criteria.CriteriaID = VIC_2.CriteriaID
and VIC_2.Destination = @.DESTINATION
and VIC_2.LengthOfStay = @.LENGTHOFSTAY
and VIC_2.Ages = @.AGE
and VIC_2.ComponentType = @.COMPONENTTYPE_2
and VIC_2.ValidItemType = @.VALIDITEMTYPE_2
and VIC_2.ItemValue = @.ITEMVALUE_2
Left Outer Join ValidItemCriteria VIC_3
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_3.Destination = @.DESTINATION
and VIC_3.LengthOfStay = @.LENGTHOFSTAY
and VIC_3.Ages = @.AGE
and VIC_3.ComponentType = @.COMPONENTTYPE_3
and VIC_3.ValidItemType = @.VALIDITEMTYPE_3
and VIC_3.ItemValue = @.ITEMVALUE_3
where VIC_1.CriteriaID is not null
or VIC_2.CriteriaID is not null
or VIC_3.CriteriaID is not null|||Are the matches against collections of criteria (for example, a given row needs to match any one of the vendors, any one of the site codes, and any one of the item values in order to qualify), or are the matches against sets of criteria (a row needs to match on vendor N, site code N, and criteria N in order to qualify)? That makes a considerable difference in how the code needs to work.

Do NULL values matter (do you ever need to search for a NULL criteria)? That's a really nasty twist from a performance perspective.

-PatP|||SELECT
...
WHERE
...
and CriteriaID in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and
(
coalesce(@.COMPONENTTYPE_1, @.VALIDITEMTYPE_1, @.ITEMVALUE_1) is null
OR
(ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1)
)
and
(
coalesce(@.COMPONENTTYPE_2, @.VALIDITEMTYPE_2, @.ITEMVALUE_2) is null
OR
(ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2)
)
and
(
coalesce(@.COMPONENTTYPE_3, @.VALIDITEMTYPE_3, @.ITEMVALUE_3) is null
OR
(ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3)
)
)|||I'm just curious, but would you please explain what you think that SQL will do?

-PatP|||I'm just curious, but would you please explain what you think that SQL will do?

-PatP

Its useless, I noticed the flaw myself. I realized that I was negating the first criteria if I found criteria on the second. I have changed the question to a new post with a query I belive will complish this:Optional Inner Joins.

Please help if you can.

adding date timestamp to xp_sendmail procedure

I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:

use master;
go

CREATE PROC pr_sendmail
AS

DECLARE @.DT DATETIME
SET @.DT=GETDATE()

BEGIN

EXEC xp_sendmail @.recipients = 'me@.work.com',
@.message = 'send email from SQL Server Stored Procedure.',
@.copy_recipients = 'me@.work.com',
@.subject = 'Job Started at ', @.DT

END

How do I get this to work? Thanks!@.subject = 'Job Started at ' + cast(getdate() as varchar)

you can also use CONVERT instead of CAST to format the date in various different formats.

Adding column to Merge Pub

I have a merge publication (SQL 2k) and needed to add a column to a
replicated table. I used the stored proc and settings below and it seemed
to work fine. When I synched with a subscriber, it seemed to have applied
the table modification first, then downloaded the updates that were on the
publication. I just want to make sure I am doing this correctly before I run
on the production database. Thanks.
David
exec sp_repladdcolumn @.source_object = 'ConfigFA'
, @.column = 'NextCaseNumber'
, @.typetext = 'int NOT NULL DEFAULT 1'
, @.publication_to_add = 'MCFIDataPub'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 1
That is exactly right, it has to do the table modification first to pull the
data back to the publisher and then merge it there. The data on the
publisher will already have the changed format.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u163fB4HGHA.2680@.TK2MSFTNGP09.phx.gbl...
>I have a merge publication (SQL 2k) and needed to add a column to a
>replicated table. I used the stored proc and settings below and it seemed
>to work fine. When I synched with a subscriber, it seemed to have applied
>the table modification first, then downloaded the updates that were on the
>publication. I just want to make sure I am doing this correctly before I
>run on the production database. Thanks.
> David
> exec sp_repladdcolumn @.source_object = 'ConfigFA'
> , @.column = 'NextCaseNumber'
> , @.typetext = 'int NOT NULL DEFAULT 1'
> , @.publication_to_add = 'MCFIDataPub'
> , @.force_invalidate_snapshot = 0
> , @.force_reinit_subscription = 1
>