SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help needed to Process Optimization


Help needed to Process Optimization

Author
Message
sivaramakrishnan G
sivaramakrishnan G
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 92
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_ID numeric(18, 0) NOT NULL,
PAY_DATE datetime NOT NULL,
PAY_GROUP_ID numeric(10,0) NOT NULL,
MONTH numeric(2, 0) ,
YEAR numeric(4, 0) ,
COMPONENT_ID numeric(10, 0) NOT NULL,
COMPONENT_CODE varchar(10) ,
COMPONENT_TYPE varchar(1) ,
CALCULATIOTYPE varchar(1) ,
ACTUAL_AMOUNT numeric(23,3) ,
EARNED_AMOUNT numeric(23,3) ,
LOP_AMOUNT numeric(23,3) ,
ARREAR_AMOUNT numeric(23,3) ,
ADJUSTED_AMOUNT numeric(23,3) ,
LAST_MONTH_ROUNDOFF numeric(23,3) ,
CURRENT_MONTH_ROUNDOFF numeric(23,3) ,
CURRENCY_CODE varchar(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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213865 Visits: 46263
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sivaramakrishnan G
sivaramakrishnan G
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 92
Thanks Gila for your reply.We are processing the process using Stored Procedure.Can i Share the Same.
sivaramakrishnan G
sivaramakrishnan G
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 92
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_id and
fix.employee_id=pro.employee_id and
pro.employee_id=proemp.employee_id and
fix.component_id = comp.component_id and
comp.component_id = pro.component_id and
pro.pay_group_id=emp.pay_group_id and
emp.pay_group_id=comp.pay_group_id and
comp.pay_group_id=proemp.pay_group_id and
emp.pay_group_id = @mpaygroupid and
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
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39663 Visits: 19992
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search