What are the low level implementation details that make set based operations exponentially faster than iterative processes, like cursors?
At a memory level how does a set operation "touch" the data less than a cursor?
$\begingroup$
$\endgroup$
0
Add a comment
|
2 Answers
$\begingroup$
$\endgroup$
Set-based operations are iterative at a "low level", 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 "simple" or "fast" 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.
$\begingroup$
$\endgroup$
When operations are made on sets, this is what DBMS engine is optimized to do, all fancy stuff like collecting minimal intersection from queries, order execution, batch reads and possible concurrency kicks in.
Per row query could be executed by multiple threads, splitting work among them, when you create cursor processing must be sequential. By the nature of temporarily created set, it differs from internal structure by allowing iterator-like traversal, so it is effectively new table populated with result. When query is executed all updates are issued, so even not altered data (in the cursor) is updated back, this creates more work - the price for easier result traversal.
No possibility for parallel execution is constant slowdown, creating temporary memory is operation with huge constant, updating results is wasteful, but this creates another passes through data, so slowdown is polynomial with one-thread processing and additional memory management.