A Query Method Faster Than BETWEEN
-
Posted on April 2, 2012 by Derek Dieter
-
0
This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap.
While this describes one particular scenario for this example, there are many other situations this method can be used.
Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time.
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND '2001-04-28' BETWEEN StartDate AND EndDate
This query will return to us the single record that is active as of ’2001-04-28′. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee.
Here’s an example:
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND StartDate >= '2001-04-28'
ORDER BY StartDate
Because only one record can be active for an employee at a single point in time, we can be sure that if we take the minimum start date that is less than or equal to the as of date, this will also return the active record. Since this query only needs a one column in order to isolate the record, it can be much quicker than our BETWEEN version.
If we wanted to be sure this date range fell within our as of date, would could also use this fail safe:
FROM (
SELECT TOP 1 *
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND StartDate >= '2001-04-28'
ORDER BY StartDate
) t
WHERE '2001-04-28' <= EndDate
While this trick may show marginal improvements in some tables, it can be many many times faster in other scenarios. It all depends on how many versions of a single record exist, and the size of the table. I recently employed this method that took a query down from 30 ms, to under half of one millisecond.
- Comments (RSS)
- Trackback
- Permalink
Hi everybody,
good article Mr Dieter.
In the 2nd and the 3rd query shouldn’t you write
StartDate >= ’2001-04-28′ instead of StartDate <= '2001-04-28' ?
Cheers !
V.
Yes, you’re correct. Thank you Val
