Chapter 35. You see sets, and I see loops

 

Linchi Shea

Within the SQL Server community, the predominant view on writing T-SQL for performance is that you should think in sets instead of procedures. In addition, it follows that you should focus on what result you want in terms of the set operations, and let SQL Server figure out how to get the result under the hood. This view has served the community well, especially when developers versed in indexed sequential access method (ISAM)-style databases, such as Microsoft Access, migrate to SQL Server and carry with them the habit of interacting with databases on a row-by-row basis. The set-oriented approach is an effective counter measure to minimize the potential damage to SQL Server performance by the ISAM-style interaction.

That was then, and this is now. The SQL Server community has been thoroughly educated in the set-oriented approach. In fact, having become comfortable in expressing complex logic in the set-based SQL constructs, some SQL developers may have carried the set orientation, as wise and sound as it is, to the extreme and gone overboard. For the past few years, I’ve seen increasing evidence, in real-world applications, of the performance damage done by overzealous application of the set-oriented approach, the symptom of which is often overly complicated SQL queries.

What loops?

The loop perspective

Loops in a query execution plan

Loops in complex queries

User-defined scalar functions in implicit loops

Merging multiple loops into one

Parallelizing loops

Linked server calls in a loop

Squeezing the fat out of loops with a slim table

Summary

About the author