Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help needed to Process Optimization Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 3:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:35 PM
Points: 14, Visits: 91
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
Post #1532945
Posted Tuesday, January 21, 2014 5:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,881, Visits: 36,228
Could you post the queries?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1532987
Posted Tuesday, January 21, 2014 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:35 PM
Points: 14, Visits: 91
Thanks Gila for your reply.We are processing the process using Stored Procedure.Can i Share the Same.
Post #1532994
Posted Wednesday, January 22, 2014 6:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:35 PM
Points: 14, Visits: 91
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
Post #1533587
Posted Wednesday, January 22, 2014 6:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
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
Post #1533595
Posted Wednesday, January 22, 2014 11:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:28 AM
Points: 43, Visits: 509
please post include actual execution plan.


===========================================
performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1533912
Posted Wednesday, January 22, 2014 11:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:28 AM
Points: 43, Visits: 509
please post actual execution plan.


===========================================
performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1533913
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse