Thursday, September 26, 2013

Debunking the myth that CTE is just a cursor

Some devs who have fervent belief that CTE is just a cursor got me worked up


Hearing that broken theory from them, I decided to create a proof-of-concept that would prove otherwise. As we only have limited keystrokes left in our hands, I decided to practice first at play.typeracer.com, being able to type fast is certainly a win if one is very worked up to quickly prove something by quickly making a proof-of-concept and quickly blogging it afterwards, all needed be done on one sitting. So that's how seriously worked up I am to prove something lol


Heheh scratch that, I already made an analysis and proof an eon ago that CTE is not slow.


A CTE is just an inline view, and in turn is just a table-deriving query, blogged it here: http://www.ienablemuch.com/2013/07/debunking-myth-that-cte-is-slow.html


CTE is not slow, not understanding the intent and how the query works is what makes a query slow, blogged it here: http://www.ienablemuch.com/2012/05/recursive-cte-is-evil-and-cursor-is.html



Please do note that a **recursive** CTE is being done sequentially, hence the "cursor-like" belief of some devs, however, the recursive CTE sequential steps is not being done on cursor. The CTE's loop is made on C++, which makes CTE faster, while a CURSOR's loop is being done via T-SQL, T-SQL loop is slow. C#'s loop is even an order of magnitude faster than T-SQL loop. Push the loop on C++, i.e., push the loop on CTEs. For non-recursive CTE, rest assured, the query still operates in set-based manner, not in cursor-like manner.



Time to stop spewing the nonsense that CTE is just a cursor.



Happy Coding!

No comments:

Post a Comment