looking for some hints on SP performance

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,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||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@.sxnews1.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
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.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(DATETIME,CONVERT(VARCHAR(8),@.START_DATE),101),CONVERT(DATETIME,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(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.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(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_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(Resources.LogonName)-CHARINDEX('\',Resources.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.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
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(SSIU.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.UserUsageProgramGroup 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(SSIU.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,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.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
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.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@.sxnews1.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
>