Function vs procedure decision in SQL - Performance Comparison

Time Complexity: Function vs procedure decision

O(n)

Understanding Time Complexity

When choosing between a function and a procedure in SQL, it's important to understand how their execution time grows as the data size increases.

We want to know which one might take more time when working with larger inputs.

Scenario Under Consideration

Analyze the time complexity of these two SQL blocks.

-- Function example
CREATE FUNCTION get_total_sales(customer_id INT) RETURNS DECIMAL LANGUAGE SQL AS $$
  SELECT SUM(amount) FROM sales WHERE customer_id = $1;
$$;

-- Procedure example
CREATE PROCEDURE update_customer_sales(customer_id INT) LANGUAGE plpgsql AS $$
BEGIN
  UPDATE customers SET total_sales = (SELECT SUM(amount) FROM sales WHERE customers.customer_id = customer_id) WHERE customers.customer_id = customer_id;
END;
$$;
    

The function calculates total sales for one customer. The procedure updates a customer's total sales in the customers table.

Identify Repeating Operations

Look at what repeats when these run.

• Primary operation: Scanning the sales table to sum amounts for a customer.
• How many times: Once per call for the function; once per procedure call, but procedure may be called multiple times in a batch.

How Execution Grows With Input

As the sales table grows, the time to sum sales for one customer grows roughly with the number of sales that customer has.

Input Size (number of sales for one customer)	Approx. Operations
10	10 sums
100	100 sums
1000	1000 sums

Pattern observation: The time grows linearly with the number of sales for that customer.

Final Time Complexity

Time Complexity: O(n)

This means the time to calculate or update sales grows directly with the number of sales records for the customer.

Common Mistake

[X] Wrong: "Functions always run faster than procedures because they return values."

[OK] Correct: Both run similar queries; speed depends on what they do inside, not just if they return a value.

Interview Connect

Understanding how functions and procedures perform helps you choose the right tool for your database tasks, a skill valued in real projects and interviews.

Self-Check

"What if the procedure updated total sales for all customers at once? How would the time complexity change?"