Inverse Predicates – look both ways before you cross

When I’m at the PASS Summit in Seattle this week, I will need to remember that I can’t just step onto the road if I’m walking along the footpath on the right-hand side. In the UK and Australia, where we drive the correct side, it’s fine (just don’t tell my kids), because the cars that are nearest me are coming towards me – except of course, the idiots who are reading blog posts while they’re driving. They could be anywhere.

I should also learn to stop picking on left-handed people for being ‘sinister’. The word ‘sinister’ comes from the Latin word for left, and is associated with the fact that writing with your left hand is unnatural – some would even use the word ‘evil’. My beautiful wife is left-handed, and she’s definitely not sinister or evil. So really I should just pick on left-handed people for their inability to write on whiteboards, not for their sinisterisity.

You see, the world is designed for right-handed people. Even cars are designed to be driven by right-handed people, and this is why in normal places, the steering wheel is positioned on the right-hand side of the car so that the right-hand can be in control of it all the time, with the left hand being free to change gears, use the indicator, change the radio stations, drink coffee, tweet, etc – all those things that are less important than controlling the car.

And so we consider queries – in particular, the joining of tables. We might include something in our FROM clause like:

This aligns nicely with our thoughts around SARGability, which suggests that you leave the left-hand side of predicates untouched. Unfortunately, I don’t tend to write my ON clause predicates this way around, because I like to focus on the table I’ve just introduced into the mix. I write my queries like:

You’ll notice that I’ve inverted the predicate, because I still like the idea of keeping the left-hand side untouched, but at this point, I want to ask you to have a think about what we’re trying to achieve.

I’ve written before about how the point of SARGability is to allow an index to be used nicely. Think of the phone book, and how you can easily find the name you want (also, just for fun, think of the Japanese phone book, and how you can’t find names in it unless you understand how their alphabet is ordered). It’s very similar with our Deliveries and Orders tables – we can find a particular date in it if there is an index on that field.

But which query should we think about? The first one, where we’re trying to find a Delivery, or the second, where we’re trying to find an Order.

Naturally, being a data question, the answer is “It depends”. And that’s right – it depends on what your query is actually doing.

If you’re just creating a query, and have a WHERE clause that contains something like: WHERE o.OrderDate = ‘20101106’, then you’re okay. You know that you can easily find the Orders you want, and need the ON clause in the first example (to help find the Delivery). But if you’re creating a view, and people can use this query in a variety of ways, then you don’t know which side you’re going to start.

When SARGability is working nicely, we probably see the execution plan using an Index Seek, and can notice in that Seek’s properties that there is a Seek Predicate being used to support that. You can also see it in the ToolTip, as I’ve shown in the picture below. I ran the query SELECT * FROM Production.Product WHERE ProductID = 316;

If there are other predicates involved, that aren’t so SARGable (quite probably nothing to do with functions, rather than a choice of index), then we see this reflected in the Predicate property.

Here I’m filtering on the SafetyStockLevel as well as the ProductID. It uses the ProductID for the Seek, but checks all the results of this Seek using the SafetyStockLevel value, before returning anything out of the Seek operator. We’re not doing a scan with SafetyStockLevel, but we are checking every value returned by the Seek Predicate. This could be improved with an index across both columns (together, not individually), but if the Seek is effective, then the regular Predicate won’t have to be applied to many rows anyway.

When thinking about your indexes, do remember that included_columns (rather than key_columns) are not ordered, so any predicates involving them won’t be Seek Predicates. If the phone book were only on LastName, and FirstName was an included columnn, then finding me in there would mean checking all the farleys for Rob, rather than finding the Farleys and then continuing the seek to find the Rob entry.

Back with our orders and deliveries, we see an interesting phenomenon at play.

If I create a view using the first FROM clause, and filter the view on OrderDate, I might get a nice plan, involving Seeks.

This is an excellent plan for this particular query, but if I start with the DeliveryDate, then it’s not so nice.

The cost of this is much larger – probably orders of magnitude larger. But if I use the other ON clause, I see the same behaviour with the columns switched (good performance if I start with the DeliveryDate, bad performance if I start with OrderDate).

Ideally, we can do a Seek to find the records we want, regardless of whether we want to look up the records by DeliveryDate or by OrderDate. This would mean getting a Seek Predicate both times to see SARGability in use.

Having shown you earlier the example that used both a Seek Predicate AND a Predicate in the same query provides the key here…

Use both! (Yes, work out the inverse predicate, and include it)

The predicate which isn’t suitable for the Seek will be used as a regular Predicate, while the SARGable one gets the effective Seek Predicate. Now you can the best of both worlds, but you will need to work out the inverse of some of your predicates.

It’s not hard, but it may have great reward. In the plans below, I’ve created a view like the notice the fact that when we look up records with a known OrderDate, we can seek for the Order, and then look up the Delivery using the transformed OrderDate. Checking that the OrderDate matches the transformed DeliveryDate falls into the Predicate, not the Seek Predicate.

Conversely, if we have the DeliveryDate, then we can seek to find the Delivery, and then look up the Order using an appropriate Seek Predicate, with the other becoming the regular Predicate.

So now when you want to create views for a variety of uses, you can hopefully see some potential in saying the same thing from both perspectives. Don’t just think left-to-right – consider all the different ways you could approach your data. Just maybe don’t try to write your execution plan on a whiteboard.