Mark Hickin (1/17/2006)
I think it's worth making the point that this approach does nothing to combat what is fundamentally bad about cursors. All we have done here is createda cursor, without using the declare cursor syntax.
The article states:
<However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario>
Yes, but this still isn't set based is it? It's still doing everything row by row, which is exactly what we want to avoid.
Much better to take a step back and see if you process many, or all of the purchase orders in a small number of set based sql statements, and try not to take such a procedural approach.
I'd also say that the 75% performance improvement is highly dubious. How can this be? Presumably, the work is being done within the usp_generate_purchase_order procedure. Are we really saying that the looping code was the bottleneck? If this process took 1 min originally, are we saying that by substituting the cursor for the while loop reduced it to 15 secs? I really don't think so.
I'm not saying that this construct should never be used. There are many cases when you might want to slow things down (archiving/purging/data movingfor example) to aid concurrency and reduce the size of transactions, but this is not a good pattern to use in high performance database programming.
I would guess that the difference was their cursor inspected more rows.
Putting only the rows that needed operation into the in memory table and then running through the loop is the best option for any loop.
I would wonder why not use CTE ( as much as I dislike them).