Efficiently Filter Large Set With Disjunctions

Question

Let's say I have a single table

CREATE TABLE Ticket (
    TicketId int NOT NULL,
    InsertDateTime datetime NOT NULL,
    SiteId int NOT NULL,
    StatusId tinyint NOT NULL,
    AssignedId int NULL,
    ReportedById int NOT NULL,
    CategoryId int NULL
);

In this example TicketId is the Primary Key.

I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed:

1. The query will always perform a range filter on an InsertDateTime
2. The query will always ORDER BY InsertDateTime DESC
3. The query will page results

The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example:

SELECT
    TicketId
FROM (
    SELECT
        TicketId,
        ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum
    FROM Ticket
    WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01'
      AND StatusId IN (1,2,3)
      AND (CategoryId IN (10,11) OR CategoryId IS NULL)
    ) _
WHERE RowNum BETWEEN 1 AND 100;

Now assume the table has 100,000,000 rows.

The best I can come up with is a covering index that includes each of the "optional" columns:

CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime DESC
) INCLUDE (
    SiteId, StatusId, AssignedId, ReportedById, CategoryId
);

This gives me a query plan as follows:

• SELECT
  • Filter
    • Top
      • Sequence Project (Compute Scalar)
        • Segment
          • Index Seek

It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal.

Are there better strategies for implementing this kind of searching?

I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client.

Answer 1

If the clients are filtering in almost the same way over and over again you can create an index for those queries.

E.g. the client is filtering on SiteId and StatusId you can create an additional index:

CREATE NONCLUSTERED INDEX IX_Ticket_InsertDateTime_SiteId_StatusId ON Ticket     
(InsertDateTime DESC,
 SiteId [ASC/DESC],
 StatusId [ASC/DESC] ) 
 INCLUDE ( ... );

This way, most of the 'more common' queries could run fast.