Search results matching tags 't-sql tuesday', 'indexing', and 'sql'

Joins in single-table queries

rob_farley — Tue, 11 Dec 2012 00:05:17 GMT

Tables are only metadata. They don’t store data.

I’ve written something about this before, but I want to take a viewpoint of this idea around the topic of joins, especially since it’s the topic for T-SQL Tuesday this month. Hosted this time by Sebastian Meine (@sqlity), who has a whole series on joins this month. Good for him – it’s a great topic.

In that last post I discussed the fact that we write queries against tables, but that the engine turns it into a plan against indexes. My point wasn’t simply that a table is actually just a Clustered Index (or heap, which I consider just a special type of index), but that data access always happens against indexes – never tables – and we should be thinking about the indexes (specifically the non-clustered ones) when we write our queries.

I described the scenario of looking up phone numbers, and how it never really occurs to us that there is a master list of phone numbers, because we think in terms of the useful non-clustered indexes that the phone companies provide us, but anyway – that’s not the point of this post.

So a table is metadata. It stores information about the names of columns and their data types. Nullability, default values, constraints, triggers – these are all things that define the table, but the data isn’t stored in the table. The data that a table describes is stored in a heap or clustered index, but it goes further than this.

All the useful data is going to live in non-clustered indexes. Remember this. It’s important. Stop thinking about tables, and start thinking about indexes.

So let’s think about tables as indexes. This applies even in a world created by someone else, who doesn’t have the best indexes in mind for you.

I’m sure you don’t need me to explain Covering Index bit – the fact that if you don’t have sufficient columns “included” in your index, your query plan will either have to do a Lookup, or else it’ll give up using your index and use one that does have everything it needs (even if that means scanning it). If you haven’t seen that before, drop me a line and I’ll run through it with you. Or go and read a post I did a long while ago about the maths involved in that decision.

So – what I’m going to tell you is that a Lookup is a join.

When I run SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesPersonID = 285; against the AdventureWorks2012 get the following plan:

I’m sure you can see the join. Don’t look in the query, it’s not there. But you should be able to see the join in the plan. It’s an Inner Join, implemented by a Nested Loop. It’s pulling data in from the Index Seek, and joining that to the results of a Key Lookup.

It clearly is – the QO wouldn’t call it that if it wasn’t really one. It behaves exactly like any other Nested Loop (Inner Join) operator, pulling rows from one side and putting a request in from the other. You wouldn’t have a problem accepting it as a join if the query were slightly different, such as

SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesPersonID = 285;

Amazingly similar, of course. This one is an explicit join, the first example was just as much a join, even thought you didn’t actually ask for one.

You need to consider this when you’re thinking about your queries.

But it gets more interesting.

Consider this query:

SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276
AND CustomerID = 29522;

It doesn’t look like there’s a join here either, but look at the plan.

That’s not some Lookup in action – that’s a proper Merge Join. The Query Optimizer has worked out that it can get the data it needs by looking in two separate indexes and then doing a Merge Join on the data that it gets. Both indexes used are ordered by the column that’s indexed (one on SalesPersonID, one on CustomerID), and then by the CIX key SalesOrderID. Just like when you seek in the phone book to Farley, the Farleys you have are ordered by FirstName, these seek operations return the data ordered by the next field. This order is SalesOrderID, even though you didn’t explicitly put that column in the index definition. The result is two datasets that are ordered by SalesOrderID, making them very mergeable.

Another example is the simple query

SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276;

This one prefers a Hash Match to a standard lookup even! This isn’t just ordinary index intersection, this is something else again! Just like before, we could imagine it better with two whole tables, but we shouldn’t try to distinguish between joining two tables and joining two indexes.

The Query Optimizer can see (using basic maths) that it’s worth doing these particular operations using these two less-than-ideal indexes (because of course, the best indexese would be on both columns – a composite such as (SalesPersonID, CustomerID – and it would have the SalesOrderID column as part of it as the CIX key still).

You need to think like this too.

Not in terms of excusing single-column indexes like the ones in AdventureWorks2012, but in terms of having a picture about how you’d like your queries to run. If you start to think about what data you need, where it’s coming from, and how it’s going to be used, then you will almost certainly write better queries.

…and yes, this would include when you’re dealing with regular joins across multiples, not just against joins within single table queries.

SARGability isn’t about the left-hand side

rob_farley — Tue, 12 Oct 2010 22:10:12 GMT

This month’s T-SQL Tuesday has me caught slightly unawares. I’m in the UK, having come over for SQLBits, and I’m left writing this with only a few hours to go.

The theme this time is on misconceptions in SQL, and this fits nicely with SARGability – one of the topics that I presented on at SQLBits a week and a half ago. I often see people refer to SARGability as making sure that the left-hand side of the predicate is untouched. In other words, suggesting that something like this would be fine:

WHERE someColumn = someFunction(someValue)

whereas something like this would not:

WHERE someOtherFunction(someColumn) = someValue

As with most misconceptions, there’s an element of truth to this, but it definitely doesn’t tell the whole story.

If you have an index on someColumn, then great – the first predicate should let you find the value you want in that index nicely. Then you have other questions about whether the index is suitable or not, but that’s for a different post. On the other hand, if you don’t have an index, then the system is going to scan the table (or at least some less suitable index) in both scenarios. Applying the function to each column is painful in the second one, but SARGability is about producing seeks instead of scans, and these would both cause scans.

But how often do you write predicates like this. Normally, we have something more like:

ON x.someColum = y.someOtherColumn

…and this naturally causes a question. Do we want to be looking up the values in x, or in y? What needs to be SARGable here, the left or the right? It’s very easy to assume the left, but is it really going to be best for you? SQL Server doesn’t care which way around you’ve written your predicate, so don’t hold onto the ‘left-hand side’ idea longer than the end of this post. In fact, think about the way you want your query to be executed, and ask yourself where your SARGability should actually be looking.

It’s all a matter of perspective…

Table? No such thing…

rob_farley — Mon, 13 Sep 2010 23:48:00 GMT

No really – hear me out.

Of course you create tables, and you query tables, and we say that data is stored in tables. The table is (rightly) a fundamental part of relational theory. But I find that when I think about queries and how they run, I need to approach the system thinking about the indexes that I’m querying, not the tables.

When you decide to find someone’s phone number, you have to make a decision about where you’re going to look that up. Do you pick up the White Pages, the Yellow Pages, your little black book, your kids’ school’s parent directory, or something else? In fact, this is a question that you ask yourself before you even consider whether the particular information is accessible or not. If you had no other option but to scan the entire White Pages (starting at page 1), you would probably just consider the information unobtainable.

And yet all these different sources of data are just different non-clustered indexes on the master set. They’re generally filtered, ordered in an appropriate way, and don’t contain all the information. The Yellow Pages is just businesses, ordered by business type. The White Pages doesn’t include the business type at all, and maybe the school’s parent directory doesn’t include the address. Each of them is designed for a particular type of use. The cost of maintaining them is considered (and presumably it’s understood that the benefits of having the index far outweighs of cost).

I know some of you will be telling me that not all tables have indexes – but every table in SQL Server is either a Heap or a Clustered Index, and if you consider that a Heap is simply an unordered index, then hopefully my points can stand.

When you write a query, you have the option of forcing a particular index to be used with a Hint. This is the kind of hint where you don’t get a choice. I’m sure you all know times when someone has “hinted” that a particular task needs doing, or that you should avoid telling a particular kind of joke, or etc. The kind of hint that if you can’t obey, an error situation will occur. So if you “hint” that a particular index should be used but that index isn’t there, you’ll get an error. So I try not to use index hints if possible.

But my dislike for index hints doesn’t stop me from wanting to consider indexes rather than tables. If I think about what data I want, I really ought to be thinking about the ideal index for my query, and expect to see that index being used in the execution plan. If it’s not being used, or it’s being used in a way that I didn’t expect, then I really ought to ask myself why and investigate the situation (and consider whether or not the way that the query is being run is acceptable or not – remember the analogy of not wanting to start on page one of the White Pages). Hopefully I’ll realise that the index I imagined wasn’t actually ideal when considering the statistics involved, or that the Query Optimizer has found some clever way to answer my query in a way that I didn’t expect, but sometimes you find that the way you’ve written the query is actually persuading the Query Optimizer to create a plan that’s not actually what you want. Correct, valid, but not necessarily ideal.

It’s very easy to just think in terms of tables. You need to reference tables in your query, which ultimately is a logical construct – but the plan that gets produced is what determines the performance, the locking, the cache use, etc, and the plan doesn’t talk about tables (and if it does, the word Table means Heap). By all means you should remember that two indexes that are based on the same ‘table’ may refer to the same data, and a perfectly valid plan could use a different index to what you’re expecting – but this doesn’t mean you shouldn’t give thought to what you consider the ‘correct’ index to be.

So move on from tables, and think in terms of indexes. You’ll learn more about how your queries get executed, and your indexing techniques will improve wildly. More importantly though, your queries will almost certainly run faster.

But to complete the story, it's also completely valid to think of every index as a table. Each non-clustered index could be thought of as a table which the key is like the table's clustered index, and the NCIX's included columns are like the rest of the columns in the table. This helps you think about the cost associated with maintaining your indexes, and also helps you plan them. I'm not saying that you should create different tables instead of indexes by any means, but when you consider the idea that a table is an index, also consider that each index is a table - just to complete the picture.