Thursday, May 7, 2015

Table Variables very slow performance

My Dear,

i have a payroll calculation application that should handle many logical details at a very complex various options.

the point is explained as follows:

1- i have a function that calculate a single payroll element and returns its value for a selected month ([Calculate payroll element] Returns Float).

2- i have a function ([calculate employee payroll] return table variable) this is responsible to check what calculations needs to happen for a selected employee and calls first function for each of them.

3- i have a complex procedure that is responsible to show all company payroll as a pivot display, to do that i am:

a- looping using a cursor for each employee and checking many options (like terminated employees or payroll suspended       employees ...)

b- for each employee call the function (2) that should calculate employee payroll and insert the calculation in a temp table.

c- the Pivot functionality of SQL server is not useful as using it will need you to know what are the payroll elements exactly to select them becoming a pivot columns, while my payroll elements are already variable and comes from a table. so i am creating a temp table and opening a cursor on the calculation temp table returned from previous step and making dynamic table altering to add a column for each payroll element.

final result should be like follows: 

Employee  Basic SalaryHousing Accommodation... Dynamic Columns on the bases of table definition

001 3500 1000

002 2600 2000

this process is taking 30 secs on my laptop to calculate 370 employees salary, but taking 3 minutes to calculate same data on my online VPS.

i have made many performance monitoring and invested a lot of time to trace it, and realized that inserting a vartable table result into another table variable result is taking forever, so calling the calculate payroll for each employee (2) is taking a lot of time when it is part of a loop, while calling the same function (1) in a big and fixed select statement is way much faster. i can't depend on the big fixed statment as everything in product is dynamic.

how can i at least make the performance the same Please??

No comments:

Post a Comment