Join me – Inverting Joins to Maintain SARGability

In this post, we’re going to look at a technique I first heard about from MVP and all round good guy called Rob Farley (@Rob_Farley | www.lobsterpot.com.au). He called it “Inverse Predicates” and some of you may have seen me speak about it a few times in the past at user groups and also SQLBits conferences.

So this concept is a work around for SARGability issues, that is the ability to use indexes. You see SQL Server can only use an index on a field if it can guarantee that the order of that data has not been altered, otherwise the index would be meaningless.

This means that most functions in SQL Server aren’t SARGable, that is they remove SQL Servers ability to use any indexes on a field.

So take the example of a online shop, they have a database with two tables, one called SalesInfo which stores information about the sale and one called ShippingInfo which stores information about when sales where shipped to the customer.

Now the MD of our imaginary sales company wants to know which sales that where shipped on a certain date where ordered the day before. That way he can see the lead times between sales and shipping for example.

So you write the query below:

SELECT * 
FROM Sales.SaleInfo AS SA
LEFT OUTER JOIN Sales.ShippingInfo AS SH
    ON SH.OrderKey = SA.OrderKey
    AND SH.ShipDate = DATEADD(dd,-1, SA.SaleDate)
WHERE SH.ShipDate = '2001-07-19'

This will give us the sales where they were sold on the 2001-07-18 and then shipped the next day on the 2001-07-19.

The query plan looks like this:

image

As you can see, we are getting an index seek on the ShippingInfo table but we are having to settle for a index scan on the SaleInfo table.

The reason for this is that the DateAdd function breaks the SARGability on the SaleDate column as it may very well change the order of the data as it is passed through.

So what can we do about this? Well we can invert the join! What do I mean by that? Well the issue is that DateAdd has broken the SARGability, so to get round that what we do is the below:

SELECT * 
FROM Sales.SaleInfo AS SA
LEFT OUTER JOIN Sales.ShippingInfo AS SH
    ON SH.OrderKey = SA.OrderKey 
    AND SH.ShipDate = DATEADD(dd,-1, SA.SaleDate)
    AND SA.SaleDate = DATEADD(dd,1, SH.ShipDate)
WHERE SH.ShipDate = '2001-07-19'

So the query is identical apart from now we have an additional join clause from the first iteration. What you’ll see is that the second and third join clauses are the inverse of each other. So what we’re saying is, give me orders where the ShipDate is equal to the SaleDate minus one day and that SaleDate is equal to ShipDate plus one day, which the same thing, just the other way round.

By doing this we now get the below plan:

image

As you can see we are now getting seeks on both sides! yay! Open-mouthed smile 

This is because we are giving the query optimiser the option to use the version of both fields that aren’t in the DateAdd functions as we inverted the join.

Well hope you enjoyed this post, any questions or comments please post them below or drop me a message on twitter (@TSQLNinja)

Enjoy!

Advertisements

4 thoughts on “Join me – Inverting Joins to Maintain SARGability

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s