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:
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:
As you can see we are now getting seeks on both sides! yay!
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)