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?
Computer Science Stack Exchange is a question and answer site for students, researchers and practitioners of computer science. It only takes a minute to sign up.Sign up to join this community
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.