The base principle of iterative coding is to write T-SQL as if it were just another thirdgeneration programming language, like C#, VB.NET, Cobol, and Pascal. In those languages, the only way to process a set of data (such as a sequentially organized file) is to iterate over the data, reading one record at a time, processing that record, and then moving to the next record until the end of the file has been reached. SQL Server has cursors as a built-in mechanism for this iteration, hence the term cursor-based code as an alternative to the more generic iterative code. Most iterative code encountered "in the wild" is written for one of two reasons: either because the developer was used to this way of coding and didn't know how (or why!) to write set-based code instead; or because the developer was unable to find a good-performing set-based approach and had to fall back to iterative code to get acceptable performance. A perceived benefit of iterative code might be that developers with a background in third-generation languages can start coding right away, instead of having to learn a radically different way to do their work. But that argument would be like someone from the last century suggesting that we hitch horses to our cars so that drivers don't have to learn how to start the engine and operate the steering wheel. Iterative code also has a real benefit-but only in a few cases.
Because the coder has to specify each step SQL Server has to take to get to the end result, it's easy to store an intermediate result and reuse it later. In some cases (such as the running totals already mentioned), this can result in faster-running code. By writing iterative code, you're crippling SQL Server's performance in two ways at the same time.
You not only work around all the optimizations SQL Server has for fast setbased processing, you also effectively prevent the query optimizer from coming up with a faster way to achieve the same results. Tell SQL Server to read employees, and for each employee read the details of his or her department, and that's exactly what'll happen. But tell SQL Server that you want results of employees and departments combined, and that's only one of the options for the query optimizer to consider. An aspect that's often overlooked in the "set-based or cursor" discussion is that they represent two extremes, and there's plenty of room for alternate solutions in between. Iterative algorithms typically use one iteration for each row in the table or query that the iteration is based on, so the number of iterations is always equal to the number of rows, and the amount of work done by a single execution of the body of the iteration equates to processing a single row. Set-based code goes to the other extreme: processing all rows at once, in a single execution of the code. Why limit ourselves to choosing either one execution that processes N rows, or N executions that process one row each?
The most basic form of set-based iteration isn't used to prevent exponential performance scaling, but to keep locking short and to prevent the transaction log from overflowing. This technique is often recommended in newsgroups when UPDATE or DELETE statements that affect a large number of rows have to be run. To prevent long-lasting locks, lock escalation, and transaction log overflow, the TOP clause is used (or SET ROWCOUNT on versions older than SQL Server 2005) to limit the number of rows processed in a single iteration, and the statement is repeated until no more rows are affected. This form of set-based iteration won't increase performance of the code. It's used to limit the impact of code on concurrency, but may make the code run slower. This form of set-based iteration isn't sophisticated enough to warrant much discussion. I merely wanted to include it for the sake of completeness. Using set-based iteration to increase performance of problematic code takes, unfortunately, more than just adding a TOP clause to the query.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Ronald Hill at 01072010
1. What can be used as a key in Python
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.