A query with a function that takes longer than the same query with a subquery

Question

I have a function to get balance for a client.

CREATE OR REPLACE FUNCTION default1.get_balance (par_customer_id DECIMAL(31, 0))
  RETURNS DECIMAL(31,15)
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
BEGIN

  DECLARE var_balance DECIMAL(31,15); 

  SELECT SUM(amount)
  INTO var_balance
  FROM default1.accounting accounting 
  WHERE accounting.customer_id  = par_customer_id
    AND (YEAR(accounting.accounting_date) >= YEAR(SYSDATE)-3 or accounting.accounting_date IS NULL)
    AND paid_date IS NULL
    AND accounting_type_id <> 2
    AND NOT EXISTS (
        SELECT 1 
        FROM default1.accounting_detail detail 
        WHERE accounting.id = detail.accounting_id 
        AND detail.paid_date IS NOT NULL);

  RETURN var_balance;

END

The performance for getting the balance of one client is good, but using the function in the request to get the balance for several clients at the same time, it becomes very slow.

SELECT default1.get_balance(customer.id), customer.*
FROM default1.customer customer
WHERE customer.id < 1000

This request takes more than 2 minutes.

When I replace a function in a query with sub-subsex, it is much faster.

SELECT 
  (SELECT SUM(amount)
    FROM default1.accounting accounting
    WHERE accounting.customer_id = customer.id
    AND (YEAR(accounting.accounting_date) >= YEAR(SYSDATE)-3 or accounting.accounting_date IS NULL)
    AND paid_date IS NULL
    AND accounting_type_id <> 2
    AND NOT EXISTS (
        SELECT 1 
        FROM default1.accounting_detail detail 
        WHERE accounting.id = detail.accounting_id 
        AND detail.paid_date IS NOT NULL)),
  customer.*
FROM 
    default1.customer customer
WHERE customer.id < 1000

This request takes about 8 seconds.

I executed both requests several times in different orders without any significant changes at runtime. Therefore, I do not think this is a caching problem.

Why does a query with a function take about 15 times longer than a query with a subquery?
Can I change a function to make it faster?

Answer 1

DB2 LUW.

, (BEGIN ... END). : BEGIN ATOMIC ... END. , RETURN :

CREATE OR REPLACE FUNCTION default1.get_balance (par_customer_id DECIMAL(31, 0))
  RETURNS DECIMAL(31,15)
  LANGUAGE SQL
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
RETURN SELECT SUM(amount)
  INTO var_balance
  FROM default1.accounting accounting 
  WHERE accounting.customer_id  = par_customer_id
    AND (YEAR(accounting.accounting_date) >= YEAR(SYSDATE)-3 or accounting.accounting_date IS NULL)
    AND paid_date IS NULL
    AND accounting_type_id <> 2
    AND NOT EXISTS (
        SELECT 1 
        FROM default1.accounting_detail detail 
        WHERE accounting.id = detail.accounting_id 
        AND detail.paid_date IS NOT NULL);

SQL PSM , .

, DETERMINISTIC, ; .