MS SQL Server - looking for some hints on SP performance

Author

looking for some hints on SP performance

Joe D

2005-06-20, 11:23 am

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.

I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?

I've updates statistics but, that hasn't made any difference.

There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?

I'm open to any other options that I can look at that may help me with this.

Thanks,
JD

pdxJaxon

2005-06-20, 11:23 am

does it use variables for it's where clause ?

Show us the sproc

Greg Jackson
PDX, Oregon

Tibor Karaszi

2005-06-20, 11:23 am

I suggest you first read up on the difference between constants, parameters and variables. In short:

Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.

Parameter to a stored procedure:
WHERE col = @parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.

Variable:
DECLARE @var int
WHERE col = @var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).

I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Joe D" <jkdriscoll@qg.com> wrote in message news:d96mem$2ame$1@s
xnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
>
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
>
> I've updates statistics but, that hasn't made any difference.
>
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
>
> I'm open to any other options that I can look at that may help me with this.
>
> Thanks,
> JD
>

Joe D

2005-06-20, 11:23 am

Ok - here is the sproc:

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo. QG_ScalableUsageDeta
il') IS NOT NULL
BEGIN
DROP PROCEDURE dbo. QG_ScalableUsageDeta
il
IF OBJECT_ID('dbo. QG_ScalableUsageDeta
il') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo. QG_ScalableUsageDeta
il >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo. QG_ScalableUsageDeta
il >>>'
END
go
CREATE PROCEDURE dbo. QG_ScalableUsageDeta
il
(

@CATALOGID INT,
@START_DATE INT,
@END_DATE INT,
@DAYSINREPORT INT = 0,
@SHOWWEBAPPS INT = 1,
@USECATEGORIES INT = 0,
@CATEGORYID INT = -999,
@MAXDAYOFWEEK INT = 7,
@BUSINESS_GROUP VARCHAR(50) = '',
@DEPT_NM VARCHAR(30) = '',
@LOCATION_NM VARCHAR(30) = '',
@USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON

SET @BUSINESS_GROUP = @BUSINESS_GROUP + '%'
SET @LOCATION_NM = @LOCATION_NM + '%'
SET @DEPT_NM = @DEPT_NM + '%'
SET @DAYSINREPORT =
DATEDIFF(d,CONVERT(D
ATETIME,CONVERT(VARC
HAR(8),@START_DATE),
101),CONVERT(DATETIM
E,CONVERT(VARCHAR(8)
,@END_DATE),101))
+1

-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @USERS) <>0)
BEGIN
INSERT INTO @TEMP1
VALUES
(CONVERT(VARCHAR(255
),SUBSTRING(@USERS,1
,CHARINDEX(',',@USER
S)-1)))
SET @USERS = SUBSTRING(@USERS,CHA
RINDEX(',',@USERS)+1
,LEN(@USERS))
END
END
ELSE
BEGIN
INSERT INTO @TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@BUSINES
S_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@DEPT_NM
))
AND L.[NAME] LIKE LTRIM(RTRIM(@LOCATIO
N_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END

SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources. LogonName,(CHARINDEX
('',Resources. LogonName)+1),LEN(Re
sources.LogonName)- CHARINDEX('',Resour
ces.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @DAYSINREPORT = 0 then -1 else
convert(float, @DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData
. ResourceTotalUsageTi
me is not null) then
ResourceGetUsageData
. ResourceTotalUsageTi
me
else GetUsageData.TotalUsageTime /
ResourceGetUsageData
. ResourceTotalUsageTi
me end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTi
me
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @TEMP1 AS T1
ON
SUBSTRING(SSIU. LogonName,(CHARINDEX
('',SSIU. LogonName)+1),LEN(SS
IU.LogonName)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @START_DATE AND @END_DATE
AND UA.DayofWeek <= @MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData

Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo. UserUsageProgramGrou
p UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @TEMP1 AS T1
ON
SUBSTRING(SSIU. LogonName,(CHARINDEX
('',SSIU. LogonName)+1),LEN(SS
IU.LogonName)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @START_DATE AND @END_DATE
AND UU.DayofWeek <= @MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo. SWCategoryMembership
SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @USECATEGORIES = 1 then
@CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @CATALOGID)
or PG.ProgramGroupType = case when @SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData
.ResourceID

Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @TEMP1 AS T1
ON
SUBSTRING(U. LogonName,(CHARINDEX
('',U.LogonName)+1),LEN(U.LogonName)-CHARINDEX('',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData
.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo. SWCategoryMembership
SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @USECATEGORIES = 1 then
@CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @CATALOGID)
or PG.ProgramGroupType = case when @SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps. ResourceID,TotalUsag
eTime DESC
go
IF OBJECT_ID('dbo. QG_ScalableUsageDeta
il') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo. QG_ScalableUsageDeta
il >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo. QG_ScalableUsageDeta
il >>>'
go

SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go

"pdxJaxon" < GregoryAJackson@Hotm
ail.com> wrote in message
news:OAKSb2adFHA.2556@TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
>
> Show us the sproc
>
>
>
> Greg Jackson
> PDX, Oregon
>

Joe D

2005-06-20, 11:23 am

Thank you, I will.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:eEipG4adFHA.3376@TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
>
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
>
> Parameter to a stored procedure:
> WHERE col = @parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
>
> Variable:
> DECLARE @var int
> WHERE col = @var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
>
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Joe D" <jkdriscoll@qg.com> wrote in message
> news:d96mem$2ame$1@s
xnews1.qg.com...
>

Sponsored Links