Optimizing BETWEEN joins

Have you ever found yourself needing to do a join on a BETWEEN condition? If so, you have probably also been struggling with performance. The optimizer in SQL Server just cannot seem to produce a good execution plan for BETWEEN. Thanks to a lot of trial and error, we were however able to find a workaround!

This workaround, using a CROSS APPLY together with a SELECT TOP 1 … ORDER BY, should prove useful outside of the Anchor Modeling community as well. The example is a draw consisting of a bunch of floating point numbers, similar to the Fisher’s noncentral hypergeometric distribution, but this works similarly for date ranges.

Published by

Lars Rönnbäck

Co-developer of the Anchor Modeling technique. Programmer of the online modeling tool. Site maintainer. Presenter and trainer.

2 thoughts on “Optimizing BETWEEN joins”

  1. Note that constructing the #sequence table only works when your intervals do not overlap. This is the extra information we give the optimizer when rewriting the query, hence making it run so much faster.

Leave a Reply

Your email address will not be published. Required fields are marked *