SQL 202-Theoretical Exection Orders

Looks like we chose the blade.

Looks like we chose the blade.

When you send a query to the server, there’s an order to the steps the server will take to retrieve the data you’re looking for.  It’s important to have an understanding on what order those steps are, so you know where to start making changes in order to minimize the time it takes to optimize the query.

If you start narrowing your results sooner in the execution plan, your overall query will run faster.  Let’s look at the theoretical execution plan orders. There are two for SQL server.  One is the “standard” the other is used when you Use UNION in your query.

The Standard Execution Order

1. FROM, JOIN, ON — Joins that limit your results first.  Try to find ways to limit the results by your JOIN statements or your ON clauses.

2.  WHERE — The next best place to limit the number of results will be your WHERE clause.  I’ve seen many queries try to limit the results with the TOP clause, rather than choosing a smaller result set in the WHERE clause.  That’s never faster. (and yes that is an absolute to me)

3. GROUP BY and aggregate functions — Grouping can be used to get a DISTINCT list, and sometimes that’s useful, but if you can eliminate rows through the previous three steps, your query will be faster overall.

4. HAVING — Filtering based on aggregate values happens after the GROUP BY has taken effect ( you can’t very well have aggregates before the grouping has happened, right?

5.  SELECT — This is where we limit the results to just the columns we want to see.  You’re limiting the “width” of the results here, not the number of rows.

6.  ORDER BY — your results are sorted, you’re not going to use this to limit your results, so knowing that this comes after the other steps doesn’t help you optimized the query… but you do need to know this step comes before the TOP statement.

7.  TOP — This is the last filter that’s applied.  As a result it’s the least useful to use to speed up a query.  I’m not going to say affecting the results by limiting it to the TOP x results won’t help…but there are better ways to limit your results (FROM/JOINs, WHERE, and HAVING).

8. FOR XML — the results are converted to XML.  This has to come after everything else.  You have to have the results before you can transform them to XML.  There’s no speed to be gained in this step.  Just know it’s last.

The Execution Order when UNIONs are Applied

The order changes when you use a UNION.  FROM, JOIN, and ON are still first, WHERE is next, Then GROUP BY, and HAVING still comes in fourth.  But that’s where it changes when you use UNION.

5. TOP — all of your query filters have to fire before the server computes the UNION.

6. UNION and SELECT — all the results of all the SELECT statements are put together, then the SELECTed columns are returned.

7.  ORDER BY comes next.  Since you have to have all the results before you can ORDER the results, it has to come after the SELECT step, just like the Standard Execution plan.

8.  FOR XML — it’s still last.

Basically TOP moves ahead of SELECT when UNIONs are in play.  It helps narrow the number of rows more when you’re dealing with what would be multiple row sets.

What’s it all About?

First, thanks for not calling me Alfie.

Basically, it should help you know where to start tuning your queries.  Look at your FROM and JOIN statements first.  Are you choosing more tables than you need.  You’re looking up names and numbers, why do you have a join to a table with cars in it?  Drop it, and watch your query speed up!

Next, look at your WHERE clause, can you be more specific about what you’re looking for?  Could you narrow it down by using an integer column instead of a VARCHAR(MAX) field?  Either way, you can speed up your query with one of these.

HAVING, then TOP… These are my last two resorts for tuning a query by rewriting it.  Usually if I can’t solve the issue through tuning the FROM, JOIN, or WHERE, I turn to indexes.  And that’s what I’m going to go into next time.  If you just keep this summary section in your mind, you’ll get by just fine.

If you have any questions send them in!  I’m here to help!

Tags:

One Response to “SQL 202-Theoretical Exection Orders”

  1. tax exemption 20110119 at 10:28 #

    Thanks for some quality points there. I am kind of new to online , so I printed this off to put in my file, any better way to go about keeping track of it then printing?

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.