In the past, I’ve often had to keep large amounts of transactional and/or inactive data around for historical reporting purposes (or maybe just because I’m a data pack rat by nature). Keeping this in its original table can make querying current data rather costly, so often the approach has been to move it to an archive table or even archive database.
This of course makes querying a bit more complex and requires the management of archiving the data itself. Filtered indexes give us an option to have many of the performance advantages of archiving the data while leaving it in place.
Let’s consider the following scenario. We have a customers table and orders table. It’s been determined that most of the time we will be querying on open orders, where the order table’s CompletedOn field is null. The following script will build the two tables:
-- Create Customers table
CREATE TABLE tblCustomers (
CustomerId INT NOT NULL IDENTITY(1, 1)
,CustomerName NVARCHAR(255) NOT NULL
CONSTRAINT PK_tblCustomers PRIMARY KEY CLUSTERED
(
CustomerId ASC
)
)
GO
-- Create Orders table
CREATE TABLE tblOrders (
OrderId INT NOT NULL IDENTITY(1, 1)
,CustomerId INT NOT NULL
,OrderDate DATETIME NOT NULL
,OrderAmount MONEY NOT NULL
,CompletedOn DATETIME NULL
CONSTRAINT PK_tblOrders PRIMARY KEY NONCLUSTERED
(
OrderId ASC
)
)
GO
-- Add foreign key constraint for CustomerId
ALTER TABLE tblOrders
WITH CHECK ADD CONSTRAINT FK_tblOrders_tblCustomers
FOREIGN KEY(CustomerId)
REFERENCES tblCustomers (CustomerId)
GO
The script below will create some test data. Depending on your machine you may want to tweak some of the parameters. As setup it will create ~100,000 customer records and randomly create between 0 and 100 order records per customer (~5,000,000 records in total).
-- Create test customers
DECLARE @CustomersTemp TABLE (
CustomerName NVARCHAR(255))
DECLARE @RecordCount INT
SET @RecordCount = 0
WHILE @RecordCount < 100000
BEGIN
INSERT INTO @CustomersTemp (
CustomerName)
VALUES (
'Customer ' + CAST(@RecordCount AS NVARCHAR))
SET @RecordCount = @RecordCount + 1
END
INSERT INTO tblCustomers (
CustomerName)
SELECT
CustomerName
FROM @CustomersTemp
-- Create test orders
DECLARE @OrdersTemp TABLE(
CustomerId INT
,OrderDate DATETIME
,OrderAmount MONEY)
DECLARE @CustomerId INT;
DECLARE @OrderCount INT
DECLARE Customers CURSOR
FOR
SELECT
CustomerId
FROM tblCustomers;
OPEN Customers;
FETCH NEXT FROM Customers
INTO @CustomerId;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCount = 0;
SET @OrderCount = CAST((RAND() * 100.0) AS INT);
WHILE @RecordCount < @OrderCount
BEGIN
INSERT INTO @OrdersTemp (
CustomerId
,OrderDate
,OrderAmount)
VALUES (
@CustomerId
,DATEADD(DAY, RAND() * 3650, '1/1/2000')
,(RAND() * 1000))
SET @RecordCount = @RecordCount + 1
END
FETCH NEXT FROM Customers
INTO @CustomerId;
END
INSERT INTO tblOrders (
CustomerId
,OrderDate
,OrderAmount)
SELECT
CustomerId
,OrderDate
,OrderAmount
FROM @OrdersTemp
CLOSE Customers;
DEALLOCATE Customers;
GO
-- Mark orders completed.
UPDATE tblOrders SET
CompletedOn = GETDATE()
WHERE OrderDate < '1/1/2009'
GO
Note that the last part of the script will mark about 90% of the orders as having been completed.
Now for a query. The query below is getting the record count for all uncompleted orders greater than $500.
SELECT
COUNT(*)
FROM tblOrders
INNER JOIN tblCustomers
ON tblOrders.CustomerId = tblCustomers.CustomerId
WHERE CompletedOn IS NULL
AND OrderAmount > 500
At this point we haven’t defined any indexes other than the primary keys of the tables. On my development server the query at this point the query is taking about 120ms.
Adding the unfiltered index below reduces that time to ~57ms.
-- Add index on OrderAmount
--DROP INDEX IX_tblOrders_OrderAmount ON tblOrders
GO
CREATE NONCLUSTERED INDEX IX_tblOrders_OrderAmount
ON tblOrders (
OrderAmount ASC
,CompletedOn ASC
)
GO
Finally, adding the filtered index below reduces the time to ~34ms.
-- Add filtered index on OrderAmount
--DROP INDEX IX_tblOrders_OrderAmount_Current ON tblOrders
GO
CREATE NONCLUSTERED INDEX IX_tblOrders_OrderAmount_Current
ON tblOrders (
OrderAmount ASC
)
INCLUDE (CompletedOn)
WHERE CompletedOn IS NULL
GO
Not only is the query execution time significantly reduced, but also the space required to store the index is reduced to about 10% in this case. This space savings could be used to add additional frequently used fields as key or included fields into the index to speed queries even more. Note that in this case I’m simply including the CompletedOn field rather than adding it as a key since it’s implicitly keyed by the WHERE clause.
If you review the execution plan for the query, you’ll notice that it automatically reverts to the unfiltered index if you remove the CompletedOn filter from the WHERE clause. Thus, you could essentially have a separate set of indexes for current and archived data.
One final note. In my experience with filtered indexes, I’ve found that filtering on a BIT field doesn’t seem to actually filter. You end up with all the rows indexed as if there were no filter on it. Has anyone else found that to be the case?
Additional Info
Click here to download ZIP file with all SQL scripts
Filtered Index Design Guidelines – Microsoft TechNet (http://technet.microsoft.com/en-us/library/cc280372.aspx)
Article detailing issues with filtered indexes and SET options - Interesting issue with Filtered indexes
