TipsMy Favourite SQL Performance Tip
Optimizing a query by making it do more
It seems counterintuitive, but there are times when asking the database to apply additional query criteria can result in significantly improved performance.
This principle works in the situation where the data being sought cannot be readily indexed, but a similar or related atribute of the data can be identified that is a superset of the data and can be obtained using an index.
Here is a relatively simple example to illustrate the principle; a table called Stores includes Latitude (Y) and Longitude (X) columns as decimal values, and these are indexed. We wish to be able to locate stores that are within, say, five miles of a given x/y coordinate.
Let us assume that a user function ("MilesApart") has been defined that allows us to determine the distance between two points, using the (somewhat complicated) 'Great Circle' algorithm. We can therefore write the following SQL statement:
-- @x and @y are the point to find stores close to
SELECT * FROM Stores s
WHERE dbo.MilesApart(s.x, s.y, @x, @y) < 5
Not unnaturally, the above will result in a full table scan. However, if we know that the search is only ever going to occur outside the Arctic and Antarctic circle (a not unreasonable assumption), then we know that each degree of lat/long is at least 60 miles. We can therefore add an additional clause that will search in a square(ish) area that is just a tad bigger than the circle it contains:
-- @x and @y are the point to find stores close to
SELECT * FROM Stores s
WHERE dbo.MilesApart(s.x, s.y, @x, @y) < 5
AND s.x BETWEEN @x - 5/60 AND @x + 5/60
AND s.y BETWEEN @y - 5/60 AND @y + 5/60
We find that the optimizer will apply the index that includes X and Y, and the apply the MilesApart test to the resulting rows. Now that's magic!