0

Let me describe the issue I am currently facing.

I have software that computes a Tax Form, using a Store Procedure. In each row we present Tax values of each employee a company has. The Procedure itself is bonded to the Form, so whatever result we get from it, goes directly to the Form.

Please note: I am not allowed to change the result table structure

The issue is present in the 3 last columns of the (here simplified) table.

EmployeeID | Special Deduction | Deductions Sum | Income Tax

Currently I had to add some extra scenarios to be added for Special Deductions column.

Although the calculations are happening correctly, the load is so heavy that each employee takes about 10-15 seconds to get calculated. Imagine a company with multiple employees and you get the point... Timeout...

Trying to refactor it, I came across 2 big issues.

PROBLEM 1. - For the Special Deduction column to be populated we need to take into account multiple scenarios using CASE. Each CASE is chosen based on some Tax amounts.

Tax however is a very heavy SELECT with multiple joins, but is always the same for the same Employee.

[ How is it possible to calculate it once per Employee, then use it in each CASE clause? ]

PROBLEM 2. - Deductions Sum is being calculated with adding some other Deductions (e.g. Social Insurance) plus the already calculated Special Deduction.

What I am doing now, is calculating the whole Special Tax again for it.

For final Income Tax then to be calculated, we need to calculate again Tax and subtract the Deductions Sum. Meaning we need to find Tax (already found it multiple times so far( and Special Deductions and Deductions Sum

[ Since we already have all the above data in previous columns, how can I simply retrieve that data from there? ]

NOTE: All the columns have multiple SELECT as well, which I cannot erase completely. Meaning that e.g. for Income Tax, to have SELECT(...) - [Deductions Sum].

NOTE2: I am currently trying to move everything into functions to at least make the code a bit more readable, but I am not sure if this will have any positive impact on performance

NOTE3: I cannot use cursors, since this will change the structure of the query completely...

PS: I am sorry for the amount of text, but I do not see much use of code examples here...

KMarinis88
  • 13
  • 3
  • 1
    Check out CTEs (Common Table Expressions). You can create a CTE with the calculations you want and need to perform once, and then select from that, using whatever cases and where clauses you want. – pmbAustin Jun 20 '19 at 16:56
  • CTE is not instantiated https://stackoverflow.com/questions/51814383/why-cte-common-table-expressions-in-some-cases-slow-down-queries-comparing-to – Serg Jun 20 '19 at 17:26
  • Functions will kill performance. If you provided a simplified version of your code, then we might be able to help. As it is, your question is too vague. – Gordon Linoff Jun 20 '19 at 18:31

0 Answers0