Archive | Designing Queries for Performance RSS for this section

Evaluate the use of row-based operations vs. set-based operations

May include but is not limited to: row-based logic vs. set-based logic, batching, splitting implicit transactions

If you try to fulfill this objective following my approach, you’d first look at MSDN, and try to get some info on the topic. You’ll fail. Then you’d google it up for yourself, and find some pretty decent articles about what’s bad in row-based logic, and what’s good in set based. You won’t find anything good about row-based, about cursors, etc.

Let’s put the bigger focus (becoming a better developer) and concentrate on the smaller (take the exam). The information above means that if you stumble into anything questioning you about the use of cursors then the right answer (on the exam) is not to use them, and find the shiny set-based alternative among the answers. I think this will be the key to success (on the exam, again).

Now what is row-based and set-based logic? Thinking row-based is thinking in a for-each loop. You take one entry at a time, examine that and throw it away. The basic entity to work with is a single object (or row, in SQL). This approach focuses on how to solve a problem to gain the results you need. Set-based logic on the other hand works with entities representing result sets. This is some kind of fire and forget style programming. You want results, and you issue an order (a SQL query) to the database engine to get those results, and get them damned fast. You don’t dirty your hands with mundane things like how to get the appropriate data (only when something is bad, then come query hints). I think this little paragraph explained the basic concepts behind row-based and set-based logic. Remember – they want you to think in result sets and simple SQL queries. Because they hate cursors and everything which is considered as row-based. So do what you asked and you get a nice cert to your wall.

As for the other two objectives I found nothing really useful or not known yet. I think you’ll have to google a lot for this objective.

Optimize and tune queries

May include but is not limited to: optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP]

This one seems to be a rather large objective, but have no fear; we’ll slowly dig through it. Let’s take a look at the optimizer hints first.

As you may already know SQL Server optimizes your queries to perform as best as possible. However even the Query Optimizer can go wrong in some particular cases. As a last resort we have query hints to force some kind of execution. Generally you won’t have to use these kind of beasts, but it won’t hurt you to know about them.

There are three major kinds of hints:

  • Join Hints
  • Query Hints
  • Table Hints

We won’t bother with join hints, because I don’t really see any possibility about them showing up on the exam. So let’s consider query hints first. You can specify query hints in the OPTION clause, at the end of a statement. There are a whole lot of them, and I don’t feel like copying them from MSDN, so search for the link at the end of this post. We’ve met with some query hints already at the transaction part. There are query hints for each locking level: READUNCOMMITTED, REPEATABLEREAD, READCOMMITTED, SERIALIZABLE can be specified in an OPTION clause.
Read More…