Subscribe to this thread
Home - General / All posts - Memoization in SQL
tjhb
10,094 post(s)
#21-Sep-17 22:48

Another general question about SQL optimization.

In a query/command context, are the results of SQL functions memoized? (Systematically / only given many repeat calls / not at all.)

(The same could be asked about script functions, but memoization seems unlikely since results can depend on things the Radian engine can't predict.--This may mean that SQL functions can't be memoized either, if they happen to call script functions.)

(I just now realise that this also bears upon the possibility of SQL functions providing random values, or time functions, for example.)

tjhb
10,094 post(s)
#21-Sep-17 23:19

The reason for asking is to know whether it is generally more efficient to do e.g.

SELECT

    CASE WHEN f(a, b) THEN ... END AS x,

    CASE WHEN f(a, b) THEN ... END AS y,

    CASE WHEN f(a, b) THEN ... END AS z

FROM t;

or to add an extra node that does nothing but gather the reused result e.g.

SELECT

    CASE WHEN c THEN ... END AS x,

    CASE WHEN c THEN ... END AS y,

    CASE WHEN c THEN ... END AS z

FROM

    (

    SELECT f(a, b) AS c

    FROM t

    )

;

Assuming that f(a, b) does significant work (though it might not return Boolean as in the example).

adamw


10,447 post(s)
#22-Sep-17 07:22

In the first example, f(a, b) might be computed multiple times. We are not assuming that multiple calls to f() with the same argument values will return the same result - that might easily be false if f(), say, uses current date and time or fetches data from a changing data source or generates random values, etc.

In the second example, f() is only computed once, so the second example is more economical, yes.

tjhb
10,094 post(s)
#22-Sep-17 07:44

Thank you Adam. I know it’s a detail but this helps.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.