How to avoid T-SQL function being called more times when needing combined results?

Question

Hello, I have two T-SQL scalar functions that both perform calculations over large sums of data (taking 'a lot' of time) and return a value, e.g. CalculateAllIncomes(EmployeeID) and CalculateAllExpenditures(EmployeeID).

I run a select statement that calls these and returns results for each Employee. I also need the balance of each employee calculated as AllIncomes-AllExpenditures.

I have a function GetBalance(EmployeeID) that calls the two above mentioned functions and returns the result {CalculateAllIncomes(EmployeeID) - CalculateAllExpenditures(EmployeeID)}. But if I do:

Select CalculateAllIncomes(EmployeeID), CalculateAllExpenditures(EmployeeID), GetBalance(EmployeeID) .... the functions CalcualteAllIncomes() and CalculateAllExpenditures get called twice (once explicitly and once inside the GetBalance funcion) and so the resulting query takes twice as long as it should.

I'd like to find some better solution. I tried:

select alculateAllIncomes(EmployeeID), AS Incomes, CalculateAllExpenditures
(EmployeeID) AS Expenditures, (Incomes - Expenditures) AS Balance....

but it throws errors:

Invalid column name Incomes and

Invalid column name Expenditures.

I'm sure there has to be a simple solution, but I cannot figure it out. For some reason it seems that I am not able to use column Aliases in the SELECT clause. Is it so? And if so, what could be the workaround in this case? Thanks for any suggestions.

Answer 1

Forget function calls: you can probably do it everything in one normal query.

Function calls misused (trying for OO encapsulation) force you into this situation. In addition, if you have GetBalance(EmployeeID) per row in the Employee table then you are CURSORing over the table. And you've now compounded this by multiple calls too.

What you need is something like this:

;WITH cSUMs AS
(
SELECT
    SUM(CASE WHEN type = 'Incomes' THEN SomeValue ELSE 0 END) AS Income),
    SUM(CASE WHEN type = 'Expenditures' THEN SomeValue ELSE 0 END) AS Expenditure)
FROM
    MyTable
WHERE
    EmployeeID = @empID --optional for all employees
GROUP BY
    EmployeeID 
)
SELECT
    Income, Expenditure, Income - Expenditure
FROM
    cSUMs 

I once got a query down from a weekend to under a second by eliminating this kind of OO thinking from a bog standard set based aggregate query.