Let the little ones join up first

The order of tables in a join - third blog in my series "From newbie to DBA"


In my last blog I promised to look at the simple way to use the execution plan. That will have to wait as I just remember another important tip I found some time ago to easily bust performance when handling large amount of data. And it leads up to one of the things I found to look for en the execution plan.

It's kinda up the ball park with my last post. Its all about giving the SQL Server the best information to work with.

When you make joins between a large table and a smaller one the order of the tables does matter. If I for instance make this query:

select * from BigTable a
inner join SmallTable b on a.Key1 = b.Key1 and a.Key2 = b.Key2

then everything is runned based on BigTable. Back to the selectivity I talked about ind the last post. You start out with a large amount of data and then you select out based on the small amount. Then yYou have a large amount of data to match up.

But if you instead use the SmallTable as base:

select * from SmallTable a
inner join BigTable b on a.Key1 = b.Key1 and a.Key2 = b.Key2

In this case you only have a small amount of data to match up. And you have already reduced the amount of data SQL Server has to go through in order to give you the result.

Its all about high selectivity. And you can see if your query uses small over large right away in the execution plan. More on that later. But you should all the time have in mind that the fastest solution is high selectivity