Published: July 4, 2025

Can a Single Philosophy Change in Your Code Increase Performance by 100 Times?

A multi-hour ETL job dropped to 3.5 minutes after one shift in approach - from row-by-row thinking to set-based thinking. Here's the story of a 100x speedup.

Hours-long ETL jobs. Databases that keep choking. Waits with no clear end in sight... Sound familiar?

We ran into exactly this not long ago, in a job that was taking over 6 hours.

The Problem: That Infamous "Query-in-a-Loop" Anti-Pattern

The original code had a structure that looked innocent in theory but was a performance killer in practice. To process millions of rows, it did this:

1. Pull the data from the main table with a CURSOR.
2. Start a LOOP.
3. Inside the loop, for that one single row, fire SELECT queries at other tables, over and over, to look up extra information.
4. Still inside the loop, INSERT that single row into the target table.
5. Move to the next row and repeat step 3 millions of times.

Where's the problem? Millions of rows means millions of round trips between the PL/SQL engine and the SQL engine. This creates a brutal cost known as a "context switch," and it eats system resources alive.

Now, what if I told you the fix wasn't to untangle that complicated loop - but to change our entire approach to the code?

That was our "Aha!" moment, the one thing that changed everything. And that single philosophy change was this:

Stop thinking row by row. Start thinking in sets.

In other words: instead of acting like a micromanager who hands the database one command at a time, act like a strategist who states what they want and lets the database figure out how.

When we translated this philosophy into code, the results were hard to believe. The 6+ hour job finished in 3.5 minutes. A speedup of over 100 times.

So How, Exactly? Here's What We Changed

• One query, one job: We threw out the dozens of SELECT queries buried inside the loop. All the data-gathering and enrichment logic went into a single, smart CURSOR built with WITH and OUTER APPLY.
• Bulk fetching (BULK COLLECT): Instead of pulling data row by row, we loaded it into memory in batches of 100,000, minimizing I/O.
• Bulk writing (FORALL): We wrote each batch of 100,000 records to the target table in a single operation using FORALL. That took our conversation count with the database from a million down to one.
• Parallel power: PARALLEL hints told the database to use all the hardware muscle it had for the job.
• Solid coding practices: Modern PL/SQL constructs like CURSOR%ROWTYPE, INDICES OF and CONSTANT made the code both more resilient to future changes and easier to maintain.

Here's what that philosophy looks like in code:

DECLARE
    -- 1. All the logic lives in one CURSOR that thinks in sets.
    CURSOR c_records IS
    WITH ...
    SELECT
        /*+ PARALLEL(16) */ ...
    FROM
        Source_Table ST ...;

    -- 2. Data is held in a collection, in batches.
    TYPE t_record_list IS TABLE OF c_records%ROWTYPE;
    v_RECORDS t_record_list;
    c_BATCH_SIZE CONSTANT PLS_INTEGER := 100000;
BEGIN
    OPEN c_records;
    LOOP
        -- 3. Data is fetched in bulk.
        FETCH c_records BULK COLLECT INTO v_RECORDS LIMIT c_BATCH_SIZE;
        EXIT WHEN v_RECORDS.Count = 0;

        -- 4. Data is written in bulk.
        FORALL i IN INDICES OF v_RECORDS
            INSERT /*+ APPEND PARALLEL(8) */ INTO Target_Table VALUES v_RECORDS(i);

        COMMIT;
    END LOOP;
    CLOSE c_records;
END;

In the end, we learned that the biggest performance win wasn't hiding in the most complex algorithm - it was in the most fundamental change of approach.

This article was originally published on Medium.