How to avoid the same calculations on column values over and over again in a select?

Question

I sometimes write SELECTs on the form:

SELECT
    a.col1 + b.col2 * c.col4 as calc_col1,
    a.col1 + b.col2 * c.col4 + xxx as calc_col1_PLUS_MORE
FROM ....
INNER JOIN ...
    ON a.col1 + b.col2 * c.col4 < d.some_threshold
WHERE a.col1 + b.col2*c.col4 > 0

When the calculations get rather involved and used up to 3-5 times within the same SELECT, I would really like to refactor that out in a function or similar in order to:

1. hopefully improve performance / make use of cache
2. avoid forgetting to update one of the 4 calculations when I at a later stage realize I need to change the calculation.

I usually have these selects within SPs.

Any ideas?

Answer 1

The query optimizer should already be optimizing the repeated evaluations from a performance perspective. But you can certainly use a CTE to improve readability/maintainability:

WITH CTE AS
(
    SELECT
        a.col1+b.col2*c.col4 as calc_col1,
        a.col1+b.col2*c.col4 + xxx as calc_col1_PLUS_MORE
    FROM ....
)
SELECT ...
FROM CTE c
INNER JOIN ... d
    ON c.calc_col1 < d.some_threshold
WHERE c.calc_col1 > 0

Answer 2

You can also stack many CTEs to build up complex expressions in layers:

WITH CTE AS
(
    SELECT
        a.col1+b.col2*c.col4 as calc_col1
    FROM ....
),
CTE2 AS (
    SELECT CTE.*
        ,calc_col1 + xxx as calc_col1_PLUS_MORE
    FROM CTE
)
SELECT ...
FROM CTE2 c
INNER JOIN ... d
    ON c.calc_col1 < d.some_threshold
WHERE c.calc_col1 > 0

Answer 3

You can also persist calculated fields and even index them.

If the calculations are mostly between different tables, try indexed views.