Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Would it be possible to place your subquery into a temp table or table variable and build that way? With my larger tables, I sometimes get stung by subqueries. Covering indexes only take you so far. –  Valkyrie Feb 5 '13 at 13:54
    
@Valkyrie that seems incredibly inefficient. Also consider that variants of this query (different parameters and different optional where clauses) will likely be executing several times a second all day long and need to return results on average in less than 100ms. We already do this, and it performs okay for now. I'm just looking for ideas on how to continue to improve performance for scalability. –  Joseph Daigle Feb 6 '13 at 12:47
    
How much do you care about using storage space? –  Jon Seigel May 16 '13 at 14:29
    
@JonSeigel it depends on how much... but I want to see any suggestions –  Joseph Daigle May 16 '13 at 20:02
2  
And what is your approach/query to get the 2nd page of the results? RowNum BETWEEN 101 AND 200? –  ypercube Jun 15 '13 at 7:42
add comment

5 Answers

If this particular work load is the majority of queries against the table you might consider:

ALTER TABLE Ticket ADD CONSTRAINT PK_Ticket PRIMARY KEY NONCLUSTERED (TicketId);

CREATE UNIQUE INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime ASC
);

Considerations:

  • can you use datetime2 (SQL 2008+; flexible precision)
  • will InsertDateTime be unique within your precision
  • if the times are not constrained unique sql will add a hidden uniquifier column of type int. This is added to all nonclutred index so they can reference the correct clustered record

Advantages:

  • Adds new rows to the end of the table
  • prevent writing the optional filter columns twice (once in the clustered, and once on the index leaf for the include)
  • majority of you time will still be on a cluster index seek with more or less filers.
  • then add other nonclustered index for most popular column pairs
share|improve this answer
add comment

I've used this technique in the past. The table wasn't nearly as big but the search criteria was more complex.

This is the short version.

CREATE PROC usp_Search
    (
    @StartDate  Date,
    @EndDate    Date,
    @Sites      Varchar(30) = NULL,
    @Assigned   Int = NULL, --Assuming only value possible
    @StartRow   Int,
    @EndRow     Int
    )
AS
DECLARE @TblSites   TABLE (ID Int)
IF @Sites IS NOT NULL
BEGIN
    -- Split @Sites into table @TblSites
END
SELECT  TicketId
FROM    (
        SELECT  TicketId,
                ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum
        FROM    Ticket
                LEFT JOIN @TblSites
                    Ticket.SiteID = @TblSites.ID
        WHERE   InsertDateTime >= @StartDate 
                AND InsertDateTime < @EndDate
                AND (
                    @Assigned IS NULL 
                    OR AssignedId = @Assigned 
                    )
        ) _
WHERE   RowNum BETWEEN @StartRow AND @EndRow;
share|improve this answer
add comment

Given your first two preconditions I'd be looking at a clustered index on InsertDateTime.

share|improve this answer
add comment

why don't you consider partitioning? It is available in SQL 2008 upwards but requires Enterprise edition (or Developer edition).

Basically, you split your table across multiple partitions and you define you partition criteria(function) would be you date range?

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.