In a DBMS what are the implementation details that make set operations faster than cursors?
Set-based operations are iterative at a &quot;low level&quot;, and are not necessarily any faster to execute than cursors under all circumstances.
The main advantage with set-based operations is that they are well-defined operators which can be analysed algebraically, and the order of operations thus rearranged or simplified by the database engine, or the exact algorithmic implementation altered - not just at the time of writing the query, but possibly later at the time of subsequent executions of the query.
What is &quot;simple&quot; or &quot;fast&quot; here is constantly re-determined by the database engine at runtime based on the statistics it collects about actual whole database performance.
By contrast, there is much less flexibility for the database engine in the case of cursors, and if every action in a database were written as a cursor then overall performance could become abysmal (or the system more recalcitrant to any changes) far sooner than when set-based operations are used which provide the database engine a large latitude for optimisation.
The most straightforward analogy is the performance of a modern optimising compiler for a high-level language, versus writing everything in assembly language, although this does not quite capture the ongoing monitoring that a database engine performs.