Click here to monitor SSC
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
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47090 Visits: 44341
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
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

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

Group: General Forum Members
Points: 14 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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19003
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