1
$\begingroup$

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?

$\endgroup$
0
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.