When it comes to performance in SQL Server, there is a common assumption that WHILE loops are bad. However, the problem is not the loop itself, but rather the operations performed within it. The issue arises when programmers with a procedural programming background use WHILE loops in conjunction with single row or RBAR (Row-By-Agonizing-Row) operations, without fully understanding the set-based methodologies of SQL.

In this article, we will explore the difference between a RBAR loop, a recursive CTE (Common Table Expression), and a set-based loop. We will use a real-life example to demonstrate how a set-based loop can significantly improve the performance of a query that originally took over 25 hours to complete.

The original problem involved a money lending business that required clients to belong to a group in order to obtain a loan. Clients could belong to different groups, but would be assigned to an employee based on their original group. The challenge was to identify the original group for each client, as there was no direct way to do so without traversing the group history hierarchy.

The initial solution used a cursor with a nested loop to update each client with the previous group until there was no previous group left. However, this approach proved to be extremely slow, taking over 25 hours to update only half of the clients’ original groups.

After analyzing the problem, the author decided to try a recursive CTE as an alternative solution. While this approach improved the performance compared to the RBAR loop, it still took several hours to complete on the real data.

The author then introduced a set-based loop, which involved updating the table based on levels rather than individual rows. This approach allowed the optimizer to estimate the number of rows correctly and resulted in a significant performance improvement. The set-based loop completed the query in just 17 seconds, making it the fastest solution among the three.

It is important to note that the RBAR loop, recursive CTE, and set-based loop are not inherently good or bad. The key is to use them appropriately and understand their strengths and limitations. In this case, the set-based loop proved to be the most efficient solution for the given problem.

It is worth mentioning that there may be other factors to consider when working with hierarchies, such as infinite loops and the Halloween problem, which were not addressed in this article but should be taken into account when implementing similar solutions.

In conclusion, while WHILE loops are often associated with poor performance in SQL Server, it is not the loop itself that is the problem. By using set-based methodologies and thinking in terms of sets rather than individual rows, it is possible to achieve significant performance improvements. Understanding the strengths and limitations of different looping techniques is crucial for optimizing SQL Server queries.

Thank you for reading this article. We hope you found it informative and helpful. If you have any feedback or questions, please feel free to reach out to us.