Professional SQL Server™ 2005 Programming

6.8. Performance Considerations

We've already touched on some of the macro-level "what's the bestthing to do" stuff as we've gone through the chapter, but, like most things inlife, it's not as easy as all that. What I want to do here is provide somethingof a quick reference for performance issues for your queries. I'll try to steeryou toward the right kind of query for the right kind of situation.

Yes, it's time again folks for one of my now famous soapboxdiatribes. At issue this time is the concept of blanket use of blanket rules.

What I'm going to be talking about in this section is aboutthe way that things usually work. Theword usually is extremely operativehere. There are very few rules in SQL that will be true 100 percent of thetime. In a world full of exceptions, SQL has to be at the pinnacle ofthat — exceptions are a dime a dozen when you try and describe the performanceworld in SQL Server.

In short, you need to gage just how important the performanceof a given query is. If performance is critical, then don't take these rulestoo seriously — instead, use them as a starting point, and then TEST, TEST, TEST!!!

6.8.1. JOINs vs. Subqueries vs. ?

Deciding between joins and subqueries (and for that matter, otheroptions) is that area I mentioned earlier in the chapter that I had a heateddebate with a coworker over. And, as you might expect when two people have suchconviction in their point of view, both of us were correct up to a point (andit follows, wrong up to a point).

The ...