In this article, I'll try to show the difference between a RBAR loop, a recursive CTE and a set-based loop. When the latter was applied to a real life problem, it improved performance of a query that ran for over 25 hours and wasn't able to complete, to a query that finished in two minutes. The table had over 15 million rows and was constantly growing.
Even if you're not a SS user, and I've not been for a few years, the discussion is worth the effort. All RDBMS worth your time implement a decent CTE, so, modulo syntax, it's all transferable. If the discussion sounds a bit like the [x]apply functions in R, well, it is.
1 comment:
Hi, Robert. Thanks for the tip.
For the most part, the SQL server example I looked up makes it look like "just" a specialized view, which then recursively references itself, as the name suggests.
Yet another instance of making the server do as much as possible, rather than dragging stuff back and forth. Thanks for the example, though, as those of us in the ORM ghetto don't see much of this sort of thing. I'm in the development minority that thinks that ORMs suck, I guess :-)
Post a Comment