0
Query Tuning (Part 2 - Query Execution Order)
Posted by Danielle Smith
on
15:14
in
Query Tuning
Hi everyone! Today's blog post will be focused around Query Execution Order and is part of my Query Tuning series.
You can find Part One here:
Query Tuning (Part 1 - Evaluating Query Performance)
It may surprise you that queries aren't executed in the order that they appear on screen (reading from left to right) but in a set order. It's very important to know the order of execution as a developer because you need to understand what the query is running before you can think about optimising it to increase performance.
The Query Execution Order is referred to as Theoretical because Tuning Query Performance may alter the execution order itself in order to optimise performance.
What has to be noted is that the UNION keyword has an impact on the execution order as well, because the UNION query returns the TOP n number of items before it is sorted.
See below 2 grids showing the theoretical execution order, one with UNION and one without (thanks to Microsoft for providing similar articles in your book Microsoft SQL Server 2008 - Database Developer Training Kit: MCTS Exam 70-433 p 196-197, ISBN: 978-0-7356-2639-3)
You can find Part One here:
Query Tuning (Part 1 - Evaluating Query Performance)
It may surprise you that queries aren't executed in the order that they appear on screen (reading from left to right) but in a set order. It's very important to know the order of execution as a developer because you need to understand what the query is running before you can think about optimising it to increase performance.
The Query Execution Order is referred to as Theoretical because Tuning Query Performance may alter the execution order itself in order to optimise performance.
What has to be noted is that the UNION keyword has an impact on the execution order as well, because the UNION query returns the TOP n number of items before it is sorted.
See below 2 grids showing the theoretical execution order, one with UNION and one without (thanks to Microsoft for providing similar articles in your book Microsoft SQL Server 2008 - Database Developer Training Kit: MCTS Exam 70-433 p 196-197, ISBN: 978-0-7356-2639-3)
WITHOUT UNION
WITH UNION
What Next...
Now that the analysis of the query has been done, tomorrow I will be talking about Graphical Execution Plans and how to interpret them to work out what's actually slowing your queries down.
As A Final Word...
Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.
Post a Comment
Please post any feedback or comments here...