Why Set-Based SQL Beats Procedural Queries: Real Performance Comparisons

Background

Writing efficient SQL queries is a major challenge; performance that looks acceptable in development often degrades in production because the data volume is much larger.

Problem

Many developers write queries using procedural thinking—mirroring Java or C# constructs such as IF, FOR, WHILE—resulting in row‑by‑row processing that is inefficient at the database level.

Procedural vs. Set‑Based Approach

Two examples compare counting the number of sales records per customer. The procedural version uses a sub‑query for each row, while the set‑based version aggregates with a join.

-- Procedural style example
SET AUTOTRACE ON
SELECT c.cust_id,
       (SELECT COUNT(*) FROM sh.sales s WHERE s.cust_id = c.cust_id) sa_count
FROM SH.CUSTOMERS c;

-- Set‑based example
SET AUTOTRACE ON
SELECT c.cust_id, COUNT(s.*) jh_count
FROM SH.CUSTOMERS c, SH.SALES s
WHERE c.cust_id = s.cust_id(+)
GROUP BY c.cust_id;

AUTOTRACE statistics show the procedural query performed 2,454,756 consistent gets versus only 742 consistent gets for the set‑based query, with a corresponding reduction in recursive calls and rows processed.

Using PL/SQL Functions

The article also presents a procedural solution that creates a PL/SQL function to compute each customer's total sales, then calls that function in a SELECT. The AUTOTRACE output again shows many more consistent gets (3,066,293) compared with the set‑based version (1,841).

CREATE OR REPLACE FUNCTION get_grand_total(p_cust_id_in IN SH.CUSTOMERS.CUST_ID%TYPE)
RETURN NUMBER IS
  r_grand_total NUMBER;
BEGIN
  SELECT SUM(amount_sold) INTO r_grand_total
  FROM sh.sales
  WHERE cust_id = p_cust_id_in;
  RETURN r_grand_total;
END;

SET AUTOTRACE ON
SELECT cust_id,
       get_grand_total(cust_id) grand_total
FROM sh.customers;

The set‑based alternative aggregates directly without a function, achieving dramatically fewer consistent gets and recursive calls.

Conclusion

Adopting set‑based SQL and batch processing reduces resource consumption, improves execution time, and scales better for large production data sets.