January 21, 2014 at 3:54 am
Dear All,
I am working in one Employee Salary Calculation Process Project.The following is my table design and Index Design.In my process Employees are grouped in to Logical Group called Paygroup.In a PayGroup Employees will be attached.If I execute the Payroll process Sequentially its working smoothly.If I Go for Parallel execution so may wait and block type analysed through Activity monitor.Some time Deadlock also noticed.I requset all your help how to over come this.Kindly help me out.If any other information let me know.Every thing will be done through Stored proedure.Insert/update will happen concurrntly in the following table.
CREATE TABLE dbo.PROCESSED_DATA(
EMPLOYEE_IDnumeric(18, 0) NOT NULL,
PAY_DATEdatetime NOT NULL,
PAY_GROUP_IDnumeric(10,0) NOT NULL,
MONTHnumeric(2, 0) ,
YEARnumeric(4, 0) ,
COMPONENT_IDnumeric(10, 0) NOT NULL,
COMPONENT_CODEvarchar(10) ,
COMPONENT_TYPEvarchar(1) ,
CALCULATIOTYPEvarchar(1) ,
ACTUAL_AMOUNTnumeric(23,3) ,
EARNED_AMOUNTnumeric(23,3) ,
LOP_AMOUNTnumeric(23,3) ,
ARREAR_AMOUNTnumeric(23,3) ,
ADJUSTED_AMOUNTnumeric(23,3) ,
LAST_MONTH_ROUNDOFFnumeric(23,3) ,
CURRENT_MONTH_ROUNDOFF numeric(23,3) ,
CURRENCY_CODEvarchar(3) ,
CONSTRAINT PK_PROCESSEDDATA PRIMARY KEY CLUSTERED
(
PAY_GROUP_ID,
EMPLOYEE_ID ASC,
PAY_DATE ASC,
COMPONENT_ID ASC
)
)
CREATE NONCLUSTERED INDEX IX1_MCOMPCODE ON dbo.PA_PROCESSED_DATA
(
COMPONENT_CODE ASC,
COMPONENT_TYPE ASC,
CALCULATIOTYPE ASC
) INCLUDE(PAY_GROUP_ID)
GO
CREATE NONCLUSTERED INDEX IX2_MPRODATA_MONTHYEAR ON dbo.PA_PROCESSED_DATA
(
PAY_DATE ASC,
MONTH ASC,
YEAR ASC
) INCLUDE(PAY_GROUP_ID)
Wait Types :
MERGE Statement Execution:
IOCOMPLETION
LOGBUFFER
Other Query :
PAGEIO_LATCH
SLEEP_TASK
CXPACKET
LCK_M_U
LATCH_EX
PAGEIOLATCH_SH
PAGEIOLATCH_UP
PREEMPTIVE_O
Regards
Siva
January 21, 2014 at 5:39 am
Could you post the queries?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2014 at 5:54 am
Thanks Gila for your reply.We are processing the process using Stored Procedure.Can i Share the Same.
January 22, 2014 at 6:15 am
Find the sample code used.
Insert will be done using If not Exist compaing Processed_data Table.
UPDATE Processed_data set
actual_amount=isnull(amount,0),
earned_amount=isnull(amount,0)
from
processed_data pro ,
employee_current emp,
salary_fixed fix,
component comp,
process_employee proemp
where
emp.employee_id = fix.employee_idand
fix.employee_id=pro.employee_idand
pro.employee_id=proemp.employee_idand
fix.component_id = comp.component_id and
comp.component_id = pro.component_idand
pro.pay_group_id=emp.pay_group_id and
emp.pay_group_id=comp.pay_group_idand
comp.pay_group_id=proemp.pay_group_idand
emp.pay_group_id = @mpaygroupidand
comp.s_calculatiotype in ('X','C','F')and
proemp.s_timestamp=@mTimestamp
-
update processed_data set
lop_amount = Case When sal.lop_days_ern>=@mstandarddays Then 0 Else
(earned_amount*12/365)*(sal.lop_days_ern-(-isnull(dojdays,0))) End,
earned_amount = Case When sal.lop_days_ern>=@mstandarddays Then 0 Else
earned_amount-((earned_amount*12/365)*(sal.lop_days_ern-(-isnull(dojdays,0)))) End
from
processed_data pro,
component_settings sett,
employee_current emp,
salary_data sal,
process_employee proemp
where
pro.employee_id=emp.employee_id and
emp.employee_id = sal.employee_id and
sal.employee_id=proemp.employee_id and
pro.component_id = sett.component_id and
pro.pay_group_id = emp.pay_group_id and
pro.d_pay_date = sal.d_pay_date and
emp.pay_group_id = @mpaygroupid and
sett.s_component_code='UNPAID_LEAVE' and
(sal.lop_days_ern>0 or isnull(sal.stddays_ern,0)<>isnull(sal.wrkdays_ern,0))and
sal.d_pay_date=@mpaydate and
proemp.s_timestamp=@mTimestamp
January 22, 2014 at 6:41 am
Before considering esoteric wait stats and such, your code would benefit from an overhaul to bring your join syntax up to date. Your first query equates to this:
UPDATE pro SET
actual_amount=isnull(amount,0),
earned_amount=isnull(amount,0)
FROM processed_data pro
INNER JOIN employee_current emp
ON emp.pay_group_id = pro.pay_group_id
INNER JOIN salary_fixed fix
ON fix.employee_id = emp.employee_id
AND fix.employee_id = pro.employee_id
INNER JOIN component comp
ON comp.component_id = fix.component_id
AND comp.component_id = pro.component_id
AND comp.pay_group_id = emp.pay_group_id
INNER JOIN process_employee proemp
ON proemp.employee_id = pro.employee_id
AND proemp.pay_group_id = comp.pay_group_id
WHERE emp.pay_group_id = @mpaygroupid
AND comp.s_calculatiotype in ('X','C','F')
AND proemp.s_timestamp = @mTimestamp
which is quite messy. I think this can be rewritten as follows:
UPDATE pro SET
actual_amount=isnull(amount,0),
earned_amount=isnull(amount,0)
FROM processed_data pro
INNER JOIN employee_current emp
ON emp.pay_group_id = pro.pay_group_id
AND emp.employee_id = pro.employee_id
INNER JOIN salary_fixed fix
ON fix.employee_id = pro.employee_id
AND fix.component_id = pro.component_id
INNER JOIN component comp
ON comp.component_id = pro.component_id
AND comp.pay_group_id = pro.pay_group_id
INNER JOIN process_employee proemp
ON proemp.employee_id = pro.employee_id
AND proemp.pay_group_id = pro.pay_group_id
WHERE emp.pay_group_id = @mpaygroupid
AND comp.s_calculatiotype in ('X','C','F')
AND proemp.s_timestamp = @mTimestamp
where I've made a point of joining each table to one other table. I'm sure I'm not alone in finding queries written in this style far easier to tune than those employing old-style joins.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply