Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance

Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.

~5 min readSQL
#SQL#Query Optimization#Performance
3D illustration of a slow single-row cursor loop beside a wide parallel set pipeline processing many rows at once

Quick Summary

A cursor processes a result set one row at a time: DECLARE names it, FETCH pulls rows, CLOSE releases it. It is the SQL equivalent of a for loop, and in a relational database it is almost always the slow path. The engine runs the plan once, then switches between the executor and the procedural layer for every row, re-evaluating expressions and often re-checking visibility. A set-based statement expresses the same intent as one plan over the whole set, so the optimiser can use hash joins, batch updates and parallel workers.

Numbers make the difference concrete. Updating 100,000 rows with a cursor can take minutes and hold locks for the full duration; the equivalent UPDATE ... FROM takes seconds and holds row locks for the length of one statement. Cursors also consume memory on the server: a WITH HOLD cursor materialises its entire result into a temporary file, and a client-side cursor in many drivers buffers the whole result in the application's memory.

Cursors are still legitimate in four places: chunked maintenance that must be bounded per batch (delete 5,000 rows, commit, repeat), per-row side effects against an external system where each row genuinely triggers work, procedural code where ordering plus early exit is part of the algorithm, and compatibility with an existing application contract. When a cursor is the right tool, keep it small, keep transactions short, and use FETCH FORWARD counts instead of one row at a time.

Basic syntax and query signatures

sql
BEGIN; -- Default is FORWARD-only, owned by the transaction DECLARE cur_orders NO SCROLL CURSOR FOR SELECT id, total FROM orders WHERE status = 'pending' ORDER BY id; FETCH FORWARD 100 FROM cur_orders; -- returns at most 100 rows FETCH NEXT FROM cur_orders; CLOSE cur_orders; COMMIT;

WITH HOLD survives COMMIT but is expensive: the remaining rows are spooled to disk.

sql
DECLARE cur_export CURSOR WITH HOLD FOR SELECT * FROM orders ORDER BY created_at; COMMIT; -- cursor still usable FETCH FORWARD 1000 FROM cur_export; CLOSE cur_export;

PL/pgSQL usually replaces the explicit cursor with a FOR loop, which is a cursor underneath.

sql
DO $$ DECLARE r record; BEGIN FOR r IN SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LOOP PERFORM process_job(r.id); END LOOP; END $$;

MySQL requires cursors inside stored programs, with an explicit handler for the end of the set.

sql
DELIMITER // CREATE PROCEDURE purge_old() BEGIN DECLARE done INT DEFAULT 0; DECLARE v_id BIGINT; DECLARE cur CURSOR FOR SELECT id FROM sessions WHERE expires_at < NOW(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done THEN LEAVE read_loop; END IF; DELETE FROM sessions WHERE id = v_id; END LOOP; CLOSE cur; END // DELIMITER ;

SQL Server separates the fast read-only form from the full one.

sql
DECLARE cur CURSOR FAST_FORWARD FOR SELECT id FROM Orders WHERE Status = 'Pending'; OPEN cur; FETCH NEXT FROM cur INTO @id; WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.ProcessOrder @id; FETCH NEXT FROM cur INTO @id; END CLOSE cur; DEALLOCATE cur;

Key comparison table

AspectSet-based statementCursor
Plan countOne plan for the whole setOne plan, executed row by row
Round tripsOneOne per FETCH
Lock durationLength of the statementLength of the loop, plus holdable spooling
Optimiser freedomFull (hash join, parallelism)Limited; row-at-a-time decisions
Server memoryBounded by work_mem per operationTemp files for WITH HOLD, buffers otherwise
Failure atomicityAll or nothingPartial unless wrapped in one transaction
Best atBulk transformsExternal per-row side effects
Cursor modeSupportsCostUse
NO SCROLL (default)FETCH FORWARD onlyCheapestStreaming reads
SCROLLFETCH BACKWARD, ABSOLUTEPlan materialisationInteractive paging
WITH HOLDSurvives COMMITFull result spooled to diskLong exports across requests
BINARYBinary row transferN/A in SQLDriver-level optimisation
Loop patternEquivalent set-based form
FOR r IN SELECT ... LOOP UPDATE ...UPDATE ... FROM (SELECT ...) s WHERE t.id = s.id
Running total per rowSUM(x) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING)
Per-row call to another tableJOIN LATERAL or a correlated aggregate
Delete in chunksDELETE ... WHERE id IN (SELECT id ... LIMIT 5000) repeated
N-level hierarchy walkWITH RECURSIVE

Practical edge cases and deep dives

1. Replacing a per-row update with UPDATE ... FROM

The most common cursor in production updates one table from another.

sql
-- Cursor version: thousands of statements, locks held throughout FOR r IN SELECT o.id, SUM(i.qty * i.price) AS t FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.id LOOP UPDATE orders SET total = r.t WHERE id = r.id; END LOOP; -- Set-based version: one statement, one plan, one lock window UPDATE orders o SET total = agg.t FROM ( SELECT order_id, SUM(qty * price) AS t FROM order_items GROUP BY order_id ) agg WHERE agg.order_id = o.id;

If the value comes from an external API rather than a table, the set-based door closes and the cursor becomes reasonable — but batch it.

2. Chunked maintenance: the one cursor nobody regrets

Deleting 5 million rows in one statement bloats the table, holds locks for minutes and can exhaust transaction ids. Chunking keeps each transaction short without a cursor at all.

sql
DO $$ DECLARE deleted int; BEGIN LOOP DELETE FROM events WHERE id IN ( SELECT id FROM events WHERE created_at < now() - interval '180 days' ORDER BY id LIMIT 5000 ); GET DIAGNOSTICS deleted = ROW_COUNT; EXIT WHEN deleted = 0; COMMIT; -- one transaction per chunk END LOOP; END $$;

Each chunk is a set-based statement; the loop only bounds the transaction. This pattern is what "use a cursor for large deletes" usually means in practice.

3. A cursor that runs forever because of a concurrent write

Under READ COMMITTED, a cursor's snapshot is taken per FETCH in some configurations and per DECLARE in others. In PostgreSQL a non-holdable cursor sees rows committed after its DECLARE only if the query re-reads them, which makes the result nondeterministic when a writer inserts matching rows during the loop. The same loop can terminate or not, depending on timing.

sql
-- Stable: pin the work set before the loop CREATE TEMP TABLE work ON COMMIT DROP AS SELECT id FROM jobs WHERE status = 'queued'; FOR r IN SELECT id FROM work LOOP ... END LOOP;

4. WITH HOLD writes the entire result to disk

sql
DECLARE cur CURSOR WITH HOLD FOR SELECT * FROM events; -- 40 GB table

At COMMIT, PostgreSQL must save the remaining rows, so -- 40 GB lands in pgsql_tmp and the COMMIT blocks until it is written. Holdable cursors are for result sets in the thousands of rows, not exports of production tables. For real exports, stream with COPY (SELECT ...) TO STDOUT or paginate with keyset ranges.

sql
-- Keyset pagination: no cursor, constant cost per page SELECT id, created_at FROM events WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 1000;

5. The N+1 that a cursor hides inside an application

A cursor in application code (fetchmany in a loop, psycopg2 named cursor, JDBC setFetchSize) is fine. The pathology is a cursor-shaped loop where each iteration issues its own query.

python
# 1 + N queries for row in cur.fetchall(): detail = db.execute("SELECT * FROM items WHERE order_id = %s", (row[0],))

One join returns everything in one plan:

sql
SELECT o.id, o.total, i.sku, i.qty FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.status = 'pending';

6. LATERAL gives you per-row computation without procedural code

sql
SELECT o.id, top.sku, top.qty FROM orders o CROSS JOIN LATERAL ( SELECT sku, qty FROM order_items i WHERE i.order_id = o.id ORDER BY qty DESC LIMIT 3 ) top;

This is the set-based answer to "for each order, take the top three items" — the case where a cursor loop is most tempting. LATERAL keeps everything in the planner, so the per-order ORDER BY ... LIMIT is still efficient.

7. Recursive CTE instead of a hierarchy cursor

sql
WITH RECURSIVE tree AS ( SELECT id, parent_id, 1 AS depth, name FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, t.depth + 1, c.name FROM categories c JOIN tree t ON c.parent_id = t.id WHERE t.depth < 10 -- guard against cycles ) SELECT * FROM tree ORDER BY depth, name;

A guard on depth prevents infinite loops from a cycle that the data model does not forbid. Cursors walking parent pointers have exactly the same hazard plus a round trip per level.

Common mistakes and runtime errors

Error / symptomCauseFix
34000 cursor does not existCursor declared in another transactionKeep DECLARE/FETCH/CLOSE in one transaction
cursor can only scan forwardFETCH BACKWARD on NO SCROLLDeclare SCROLL or page with keyset ranges
cannot open a cursor with WITH HOLD inside a functionHoldable cursor in a function/DO blockUse a client-side cursor instead
Loop takes minutes for a small tableOne statement per rowRewrite as UPDATE ... FROM
pgsql_tmp fills the diskWITH HOLD on a large resultStream with COPY, keyset paginate
Loop never endsConcurrent inserts matching the predicateMaterialise the work set first
Locks held for the entire loopOne long transactionCommit per chunk
MySQL 1329 No data - zero rows fetchedMissing NOT FOUND handlerDeclare CONTINUE HANDLER FOR NOT FOUND
SQL Server cursor is slowDefault dynamic cursorUse FAST_FORWARD or STATIC
Order not guaranteed in the loopNo ORDER BY in the cursor queryAdd explicit ordering
OPEN ... FOR EXECUTE with parameters fails at runtimeDynamic SQL type mismatchCast parameters, verify with EXPLAIN

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.