November 5, 2010 at 1:12 pm
I have a stored procedure that takes forever to run. In most cases over 30 minutes. If I take all of the code inside the proc and run it as just t-sql, it takes less than a minute. I have recompiled the SP but no effect. To run the SP I use:
exec rpt_listEmployeeMissingReceiptDetailReport
'243602', -- @ManagerName VARCHAR(50),
0, -- @Amount DECIMAL(15,3),
350, -- @MinAge INT,
460, -- @MaxAge INT,
'%', -- @LineOfBusiness VARCHAR(100),
'%', -- @BusinessUnit VARCHAR(100),
'%', -- @Department VARCHAR(100),
'whoever', -- @Organization VARCHAR(100),
'%', -- @ProductFamily VARCHAR(100),
'%', -- @Division VARCHAR(100),
'EmployeeLastName, EmployeeDepartmentName, EmployeeDepartmentExternalCode, ExpenseType, TotalMissingReceipts, TotalExpensesLines,
DollarValuMissing, DollarValuAll, Company, OrganizationCountryName, Location, LocationExternalCode, District, Division, Region,
ManagerElementType, ManagerLevel, RecordType' -- @SortBy VARCHAR(1000)
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 5, 2010 at 1:14 pm
The make up of the SP is(I didnt write it so no yelling LOL):
CREATE PROCEDURE [dbo].[rpt_listEmployeeMissingReceiptDetailReport]
@ManagerName VARCHAR(50),
@amount DECIMAL(15,3),
@minAge INT,
@maxAge INT,
@LineOfBusiness VARCHAR(100),
@BusinessUnit VARCHAR(100),
@Department VARCHAR(100),
@Organization VARCHAR(100),
@ProductFamily VARCHAR(100),
@Division VARCHAR(100),
@SortBy VARCHAR(1000)
AS
SELECT
a.ExpenseReportLineAllocationId,
a.EmployeeId,
a.ExpenseTypeId,
a.ReceiptStatus,
a.HomeAmount,
a.CreatedDate,
e.OrganizationName AS Company,
e.OrgCountryId AS OrganizationCountryId,
l.LineOfBusinessName AS Location,
l.LineOfBusinessExternalCode AS LocationExternalCode,
ff.BusinessUnit AS District,
ff.Division AS Division,
ff.ProductFamily AS Region,
f.DepartmentName AS EmployeeDepartmentName,
f.DepartmentExternalCode AS EmployeeDepartmentExternalCode,
gg.FilterableElementType AS ManagerElementType,
CASE gg.FilterableElementType
WHEN 'DEPT' THEN 1
WHEN 'LOFB' THEN 2
WHEN 'BUNT' THEN 3
WHEN 'PRFA' THEN 4
WHEN 'DIVS' THEN 5
WHEN 'ORGZ' THEN 6
ENDAS ManagerLevel
INTO #tempRecords
FROM
ExpenseReportLineAllocationReportView a,
Orge,
Department f,
LineOfBusiness l,
UserAccountdd,
Employee ee,
FilterableElementReport ff,
FilterableElementManager gg
WHERE
a.LineOfBusinessId = l.LineOfBusinessId
AND a.OrganizationId = e.OrganizationId
AND a.DepartmentId = f.DepartmentId
AND ff.LineOfBusiness = l.LineOfBusinessExternalCode
AND dd.UserAccountId = ee.EmployeeId
AND ee.EmployeeNumber = gg.EmployeeNumber
AND dd.UserAccountName = @ManagerName
AND DateDIff(day, a.CreatedDate, getDate() ) > @minAge
AND DateDIff(day, a.CreatedDate, getDate() ) < @maxAge
AND a.HomeReportAmount > @amount
AND l.LineOfBusinessExternalCode LIKE @LineOfBusiness -- [Location parameter: if no value specified by user then use default value of %.]
AND ff.BusinessUnit LIKE @BusinessUnit -- [District parameter: if no value specified by user then use default value of %.]
AND f.DepartmentExternalCode LIKE @Department -- [Approver Department parameter: if no value specified by user then use default value of %.]
AND e.OrganizationName LIKE @Organization -- [Organization parameter: if no value specified by user then use default value of %. Corresponds to Organization name]
AND ff.ProductFamily LIKE @ProductFamily -- [Region parameter: if no value specified by user then use default value of %.]
AND ff.Division LIKE @Division -- [Division parameter: if no value specified by user then use default value of %.]
AND
(
(gg.FilterableElementType = 'BUNT' AND gg.FEInstanceValue = ff.BusinessUnit)
OR (gg.FilterableElementType = 'LOFB' AND gg.FEInstanceValue = l.LineOfBusinessExternalCode)
OR (gg.FilterableElementType = 'DEPT' AND gg.FEInstanceValue = f.DepartmentExternalCode)
OR (gg.FilterableElementType = 'DIVS' AND gg.FEInstanceValue = ff.Division)
OR (gg.FilterableElementType = 'PRFA' AND gg.FEInstanceValue = ff.ProductFamily)
OR (gg.FilterableElementType = 'ORGZ' AND gg.FEInstanceValue = e.OrganizationName)
)
CREATE NONCLUSTERED INDEX IX_#tempRecords ON #tempRecords (EmployeeId, ExpenseTypeId)
SELECT
a.EmployeeId,
a.ExpenseTypeId,
SUM(CASE a.ReceiptStatus
WHEN 4 THEN 1
ELSE 0
END) AS TotalMissingReceipts,
COUNT(1) AS TotalExpensesLines,
SUM(CASE a.ReceiptStatus
WHEN 4 THEN a.HomeAmount
ELSE 0
END) AS DollarValuMissing,
SUM(a.HomeAmount) AS DollarValuAll,
a.Company AS Company,
a.OrganizationCountryId AS OrganizationCountryId,
a.Location AS Location,
a.LocationExternalCode AS LocationExternalCode,
a.District AS District,
a.Division AS Division,
a.Region AS Region,
a.EmployeeDepartmentName AS EmployeeDepartmentName,
a.EmployeeDepartmentExternalCode AS EmployeeDepartmentExternalCode,
a.ManagerElementType AS ManagerElementType,
a.ManagerLevel AS ManagerLevel
INTO #tempRecordsGroup
FROM
#tempRecords a
GROUP BY
a.EmployeeId,
a.ExpenseTypeId,
a.Company,
a.OrganizationCountryId,
a.Location,
a.LocationExternalCode,
a.District,
a.Division,
a.Region,
a.EmployeeDepartmentName,
a.EmployeeDepartmentExternalCode,
a.ManagerElementType,
a.ManagerLevel
HAVING
SUM(CASE a.ReceiptStatus
WHEN 4 THEN 1
ELSE 0
END) > 0
CREATE NONCLUSTERED INDEX IX_#tempRecordsGroup ON #tempRecordsGroup (EmployeeId, ExpenseTypeId)
ALTER TABLE #tempRecordsGroup ADD RecordType INT
UPDATE #tempRecordsGroup SET RecordType = 1
INSERT INTO #tempRecordsGroup
(
EmployeeId,
EmployeeDepartmentName,
EmployeeDepartmentExternalCode,
TotalMissingReceipts,
TotalExpensesLines,
DollarValuMissing,
DollarValuAll,
Company,
OrganizationCountryId,
Location,
LocationExternalCode,
District,
Division,
Region,
ManagerElementType,
ManagerLevel,
expensetypeId,
RecordType
)
SELECT
a.EmployeeId,
a.EmployeeDepartmentName,
a.EmployeeDepartmentExternalCode,
SUM(0),
COUNT(1),
SUM(0),
SUM(a.HomeAmount),
a.Company,
a.OrganizationCountryId,
a.Location,
a.LocationExternalCode,
a.District,
a.Division,
a.Region,
a.ManagerElementType,
a.ManagerLevel,
0,
2
FROM
#tempRecords a
WHERE
a.ReceiptStatus IN (0,1,2,3)
AND EXISTS (SELECT 1 FROM #tempRecordsGroup z WHERE z.EmployeeId = a.EmployeeId AND a.EmployeeDepartmentName = z.EmployeeDepartmentName)
AND NOT EXISTS (SELECT 1 FROM #tempRecordsGroup z WHERE z.EmployeeId = a.EmployeeId AND a.EmployeeDepartmentName = z.EmployeeDepartmentName AND a.ExpenseTypeId = z.ExpenseTypeId)
GROUP BY
a.EmployeeId,
a.EmployeeDepartmentName,
a.EmployeeDepartmentExternalCode,
a.Company,
a.OrganizationCountryId,
a.Location,
a.LocationExternalCode,
a.District,
a.Division,
a.Region,
a.ManagerElementType,
a.ManagerLevel
SET @SortBy = LTRIM(RTRIM(@SortBy))
IF (@SortBy Is Null)
BEGIN
SET @SortBy = ''
END
IF (@SortBy != '')
BEGIN
SET @SortBy = ' ORDER By ' + @SortBy
END
DECLARE @stmt NVARCHAR(4000)
SET @stmt = N'
SELECT
b.EmployeeId AS EmployeeId,
b.EmployeeFirstName AS EmployeeFirstName,
b.EmployeeLastName AS EmployeeLastName,
b.EmployeeNumber AS EmployeeNumber,
EmployeeDepartmentName,
EmployeeDepartmentExternalCode ,
IsNull(NullIf(f.ResourceObjectText,''''),''Other'') AS ExpenseType,
TotalMissingReceipts,
TotalExpensesLines,
DollarValuMissing,
DollarValuAll,
Company,
c.CountryName AS OrganizationCountryName,
Location,
LocationExternalCode,
District,
Division,
Region,
ManagerElementType,
ManagerLevel,
RecordType
FROM
#tempRecordsGroup a,
Employee b,
Country c,
ExpenseType d,
ResourceObject e,
ResourceObjectlang f
WHERE
a.EmployeeId = b.EmployeeId
And a.OrganizationCountryId = c.CountryId
And a.ExpenseTypeId = d.ExpenseTypeId
And d.ExpenseTypeCode = e.ResourceoBjectName
And e.ResourceoBjectId = f.ResourceoBjectId
And f.LanguageId = 1
' + @SortBy
EXEC dbo.sp_executesql @statement = @stmt
DROP TABLE #tempRecords
DROP TABLE #tempRecordsGroup
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 5, 2010 at 1:34 pm
What you're almost certainly running into is "parameter sniffing". Gail (GilaMonster) has a blog post that explains that nicely. Go to http://sqlinthewild.co.za/ and take a look.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 1:38 pm
I agree with Gus, I suspect parameter sniffing, although with something this complicated... it's hard to be sure. Get the execution plan for the procedure and the query and see if they're different. That'll be your indication. If they're the same, the problem is something else.
Just be sure that you're comparing execution between the two using the same parameters, especially with something like this, small changes in the data could result in very different performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2010 at 5:05 am
Yup, Gus was right on! I did some tweaking of the proc and viola! Problem solved. Thank you all for the help.:-D
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 8, 2010 at 6:13 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 8:26 am
It seems that something in the code was a dead give-away (almost) for parameter sniffing.
Care to elaborate a bit on what was the red flag ?
November 9, 2010 at 8:27 am
If it is not indiscrete, I would like to know about the changes (tweak) that you made...
November 9, 2010 at 8:33 am
I literally took thje suggestion of the page. I took the passed in parameter and assigned them to variables inside the proc.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 9, 2010 at 8:35 am
I love this forum.
November 9, 2010 at 1:07 pm
j-1064772
It seems that something in the code was a dead give-away (almost) for parameter sniffing.
Care to elaborate a bit on what was the red flag ?
The almost dead give away on parameter sniffing was the fact that the query ran just fine when copied into SSMS and run as individual steps yet took forever when run as a stored procedure.
Todd Fifield
November 9, 2010 at 1:08 pm
thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply