Function/SP running very slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function/SP running very slow

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-02 : 10:09:45

I have a front-end where clerks enter daily work and daily payroll hours. I have a function that basically has rolling sums of payroll hours, overtime, vacation, etc. The SP places sums, groups, and places these hours in the correct Pay Category column.

During the first several days of the two week pay period the function runs very quickly. The deployed report at this time can run at the same speed OR it can take over 10 minutes. At the end of the two week pay period the function can take over 10 minutes and the deployed report on Report Manager can take up to 30 minutes.

Obviously, I need to figure if there are things I can do to clean up my script to speed up execution time. Should I filter my PayrollView to records within last several weeks and not all records? Don't know if there is anything that jumps out to anyone that I can do:

USE [MercerDailyWorkTicket]
GO
/****** Object: UserDefinedFunction [dbo].[FCTN_WellService_PayrollTableFunction] Script Date: 08/11/2009 11:58:32 ******/
SET ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FCTN_WellService_PayrollTableFunction] (
@StartDate datetime,
@HomeLocation nvarchar (10)
)
RETURNS TABLE
AS RETURN (
WITH payroll
AS(
SELECT
ROW_NUMBER() OVER (PARTITION BY employeeNumber, payweek ORDER BY date,ID) AS TransNumber,
t.PayWeek, t.ID, t.Date, t.EmployeeNumber, t.EmployeeName, t.JobTitle, t.Hours, t.Hours1, t.CostCenter,
t.PayCategory, t.TimeClockID,t.Status,t.StartDate,t.Entity,t.GLType,t.PayGroup,t.PayGroupCode,
t.HourlyPayRate AS Rate, t.WorkLocation,t.HomeLocation,t.LocationGLCode
FROM VW_WellService_PayrollView2 t
WHERE Date >= DATEADD(DAY,-13,@StartDate) AND Date <= DATEADD(DAY,-0,@StartDate)
AND HomeLocation=@HomeLocation
)
SELECT
transNumber,payweek,ID,date,employeeNumber,employeename,jobtitle,hours,hours1,
ca.TotalHours,costcenter,paycategory,timeclockID,rate,worklocation,homelocation,locationglcode,
Status,StartDate,Entity,GLType,PayGroup,PayGroupCode,
CASE
WHEN TotalHours <=40 THEN (Hours +Hours1)
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN ((Hours-(TotalHours-40))+Hours1)
ELSE 0 END AS HrsREG,
CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT
from Payroll p
cross apply (
select
sum(hours) as TotalHours
from payroll
where
employeeNumber = p.employeeNumber and payweek = p.payweek and transNumber <= p.transNumber
) ca
)

Also, I'm in a position where I don't administer the production server or Report Manager. Is there anything I can have my IT department do to test how things are running when deployed? I read up a bit on trying to cache' the report or something to that effect.If anyone can give me or point me to things I can test or look at; i would greatly appreciate it. On payroll Monday mornings there are about 20 clients trying to run this same report. The location with only a few locations don't take as long as those with 40-50 employees. Anyhow thanks for any help you can provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:13:32

is VW_WellService_PayrollView2 an indexed view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-02 : 11:42:38

Here is the View. I don''t think it can be indexed since it references another View? I haven't worked with indexed Views so anything you can tell me about them would be helpful. What about the DBA running some kind of Database Tuning Advisor? Would the NOEXPAND help? Anyhow, I'm a bit clueless on this. Here is the View:

USE [MercerDailyWorkTicket]
GO
/****** Object: View [dbo].[VW_WellService_PayrollView2] Script Date: 08/11/2009 11:57:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_WellService_PayrollView2]
AS

SELECT
dtl.Date, dtl.WorkLocation, dtl.DailyWorkTicketNumber,dtl.EmployeeNumber, dtl.EmployeeName, dtl.JobTitle, dtl.ID,
CASE WHEN PayCategory IN ('VAC', 'HOL','SAFTY','SAFT','TRAVL','HAZ','BONJB') THEN 0 ELSE dtl.Hours END AS Hours,
CASE WHEN PayCategory IN ('VAC', 'HOL','SAFTY','SAFT','TRAVL','HAZ','BONJB') THEN dtl.Hours ELSE 0 END AS Hours1,
hdr.WorkLocation AS HomeLocation, dtl.CostCenter, dtl.PayCategory, dtl.PayWeek, dtl.LocationGLCode, hdr.TimeClockID, hdr.Status,
hdr.StartDate, hdr.Entity, hdr.GLType, hdr.PayGroup, hdr.PayGroupCode,
hdr.JobTitle AS JobTitHdr,
ISNULL(CDLDriver,0)CDLDriver,

CASE
WHEN dtl.PayCategory IN ('VAC','HOL') THEN hdr.HourlyPayRate
WHEN dtl.PayCategory IN ('SAFTY','TRAVL','HAZ','SAFT','BONJB') THEN 1
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'MWS' AND hdr.JobTitle IN ('Mechanic', 'Accounting Clerk IV','Accounting Clerk I' ) THEN hdr.HourlyPayRate
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'TWS' AND hdr.JobTitle IN ('Mechanic', 'Accounting Clerk IV', 'Driver') THEN hdr.HourlyPayRate
ELSE dtl.PayRate END AS HourlyPayRate

FROM MercerDailyWorkTicket..VW_WellService_PayrollView dtl
JOIN UP_TXD_IMPORT..VW_UP_WellService_PayrollHdrUnion hdr on dtl.EmployeeNumber = hdr.EmployeeNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:05:09

why are you using cross db view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-02 : 19:42:36

The actual Payroll system called Ultipro (not the Access front-end where the clerks enter daily work and pay information) is on another db. I need to pull the Employee 'home' location from this. If I pull the 'home' location from the Access front-end system then if the Employee is transferred during a two week pay period the Payroll system will split time and issue two checks, etc. We can't do this per some payroll laws or something.

I can move this info to get pulled at the Function. The initial query of the function below runs very quickly (1242 records in 1 second):

SELECT
ROW_NUMBER() OVER (PARTITION BY dtl.employeeNumber, dtl.payweek ORDER BY dtl.date,dtl.ID) AS TransNumber,
dtl.Date, dtl.WorkLocation, dtl.DailyWorkTicketNumber, dtl.EmployeeNumber,
dtl.EmployeeName, dtl.JobTitle, dtl.ID,
CASE WHEN dtl.PayCategory IN ('VAC','HOL') THEN hdr.HourlyPayRate
WHEN dtl.PayCategory IN ('SAFTY','TRAVL','HAZ','SAFT','BONJB') THEN 1
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'MWS' AND hdr.JobTitle IN
('Mechanic', 'Accounting Clerk IV','Accounting Clerk I' ) THEN hdr.HourlyPayRate
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'TWS' AND hdr.JobTitle IN
('Mechanic', 'Accounting Clerk IV', 'Driver') THEN hdr.HourlyPayRate
ELSE dtl.PayRate END AS Rate,
dtl.Hours, dtl.Hours1, dtl.CostCenter, dtl.PayCategory, dtl.PayWeek, dtl.CDLDriver,
hdr.WorkLocation AS HomeLocation,hdr.LocationGLCode, hdr.TimeClockID,hdr.Status,
hdr.StartDate,hdr.Entity,hdr.GLType,hdr.PayGroup,hdr.PayGroupCode
FROM MercerDailyWorkTicket..VW_WellService_PayrollView2test dtl
JOIN UP_TXD_IMPORT..VW_UP_WellService_PayrollHdrUnion hdr on dtl.EmployeeNumber = hdr.EmployeeNumber
WHERE dtl.Date >= DATEADD(DAY,-13,'2/26/2010') AND dtl.Date <= DATEADD(DAY,-0,'2/26/2010')
AND hdr.WorkLocation='mwsjac'

When adding the remaining of the function with the Cross Apply is where it takes forever (1242 records in 21 minutes 5 seconds).

WITH payroll
AS(
SELECT
ROW_NUMBER() OVER (PARTITION BY dtl.employeeNumber, dtl.payweek ORDER BY dtl.date,dtl.ID) AS TransNumber,
dtl.Date, dtl.WorkLocation, dtl.DailyWorkTicketNumber, dtl.EmployeeNumber,
dtl.EmployeeName, dtl.JobTitle, dtl.ID,
CASE WHEN dtl.PayCategory IN ('VAC','HOL') THEN hdr.HourlyPayRate
WHEN dtl.PayCategory IN ('SAFTY','TRAVL','HAZ','SAFT','BONJB') THEN 1
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'MWS' AND hdr.JobTitle IN
('Mechanic', 'Accounting Clerk IV','Accounting Clerk I' ) THEN hdr.HourlyPayRate
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'TWS' AND hdr.JobTitle IN
('Mechanic', 'Accounting Clerk IV', 'Driver') THEN hdr.HourlyPayRate
ELSE dtl.PayRate END AS Rate,
dtl.Hours, dtl.Hours1, dtl.CostCenter, dtl.PayCategory, dtl.PayWeek, dtl.CDLDriver,
hdr.WorkLocation AS HomeLocation,hdr.LocationGLCode, hdr.TimeClockID,hdr.Status,
hdr.StartDate,hdr.Entity,hdr.GLType,hdr.PayGroup,hdr.PayGroupCode
FROM MercerDailyWorkTicket..VW_WellService_PayrollView2test dtl
JOIN UP_TXD_IMPORT..VW_UP_WellService_PayrollHdrUnion hdr on dtl.EmployeeNumber = hdr.EmployeeNumber
WHERE dtl.Date >= DATEADD(DAY,-13,'2/26/2010') AND dtl.Date <= DATEADD(DAY,-0,'2/26/2010')
AND hdr.WorkLocation='mwsjac'
)
SELECT
transNumber,payweek,ID,date,employeeNumber,employeename,jobtitle,hours,hours1,
ca.TotalHours,costcenter,paycategory,timeclockID,rate,worklocation,homelocation,locationglcode,
Status,StartDate,Entity,GLType,PayGroup,PayGroupCode,

CASE
WHEN TotalHours <=40 THEN (Hours +Hours1)
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN ((Hours-(TotalHours-40))+Hours1)
ELSE 0 END AS HrsREG,

CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT

from Payroll p

cross apply (
select
sum(hours) as TotalHours
from payroll
where
employeeNumber = p.employeeNumber and payweek = p.payweek and transNumber <= p.transNumber
) ca

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-03 : 12:03:07

Am i going to have to write a separate report for each location that we have? Then cache' these the night before payroll is to be entered? How does this cache' work? i mean does it run the report for everything that has currently been entered for the parameters and then once the clerk enters the final records he or she will run the report again and it will only capture the last bit of data that they just entered?

Is there another way to write the script for better processing? i'm lost and as you may suspect this is a big project for my operation. thanks to any that may offer some tips or point me in the right direction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources