Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
After you've written a few more complex SQL statements where you're using joins and sub-queries, you'll probably end up wondering, well which one is better performance-wise? Is one objectively faster or slower than the other? And of course it depends on how you are doing the join and how you are doing the sub-query, but there is a way to find out. So I'm looking right now here at the example I did in the previous module, which is selecting the company with the most expensive order, one way doing it using a JOIN, Action Bicycle Specialists, and one way doing a sub-query. Same data, same result.
But what if we want to know which one is faster? Well there are quite a few ways of doing it to get fairly detailed. You can get into what's called the SQL Server Profiler, but we don't need to go that heavy. What I'm going to do is select the two blocks that I want to do. This is now considered our batch, but formally, the entire file is because there is nothing else in the file. I'm going to come up here to my toolbar and I'm going to click this button that says Display Estimated Execution Plan. Hit that, bang.
And what we are actually going to get if I drag this up a little bit is the execution plans for both of these. And the reason that I wanted to do both is so I can get them compared to each other. So it's actually telling me that the first one was 54% of the batch and the second one was 46%. Not a huge difference but not tiny either. There's definitely a difference going on there. Now the details are being shown up graphically and as they are actually must over and clicked on it will tell you things like the I/O cost and the CPU cost.
Right now of course we're on a small database and really all of this is going to just be invisible to any user, but even so, as you get more complex you'll find yourself looking at execution plans a bit more. Now what that will actually point out to you is there are certain places where it's doing what it calls index seeks. That you'll find them in both of these queries, and there are certain things that you'll find in the execution plans that are going to show up as having a significant cost, like here for example 55% was in the sort.
Okay, maybe a good thing, maybe a bad thing. Now you'll find that a lot this you won't be able to really change or play with until you've started exploring indexing, because it will expose some issues where you have to traipse through the entire table because there is no index to allow you to drill down to a particular point. But all we can get from this now, and it's still valuable, is that right now it seems that the 54%, which was the inner join, and 46% was the sub-query, so the sub query appears to be a bit of a winner here.
Formally though that is the estimated plan. You can actually check this little button on the toolbar to say Include Actual Execution Plan, and what that means it is really just the selection. It's now got the little blue line, blue box around it, and it means when I execute this again I'll get both my results, any messages that came back, and the actual execution plan, which again looks identical to the estimated one, which was 54% for the join and 46% for the sub-query. Again it's not always going to be the case. Sometimes you'll find the join would be the better way of doing it.
But starting to look at execution plans. Even if you are not at this point completely comfortable with what each particular part of it means, it's a very useful habit to start to get into.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 76790 Viewers
80 Video lessons · 130923 Viewers
52 Video lessons · 64816 Viewers
59 Video lessons · 50638 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.