Index solution for dynamic query

Question

I have a Credit/Debit table (5 millions records). The application must provide an UI View to search data providing N criteria:

• Type of date (required) e.g.. Creation date or due-date
• Date range (required)
• Status (paid, non-paid or both)
• Document number
• Customer ID
• Type of payment (credit card, money etc)

The user must provide the first two criteria, but the other may be or not be provided. So I have many combinations.

I think I will have to create many indexes such as:

• ix_search_customerID_dueDate (customerID, dueDate)
• ix_search_customerID_creationDate (customerID, creationDate)

There's uncountable combinations. I use C# and Entity Framework to generate queries based on which values was provided, that's easy to do, but I don't know how to create indexes to cover all possibilities. Is it possible or should I change the UI logic?

I read this answer so based on this I believe that if I create an index (creationDate, status, document, customerID, typeOfPayment) and the user only provides for example Customer ID the index won't work.

Answer 1

You can't make a single index that supports all of those optional parameters. You can make multiple indexes that do their best to support the most common combinations of parameters, but to support all possible combinations, the overhead is probably not worth it (and you'd have to measure the impact to the write portion of your workload - indexes aren't free).

My solution to the "customer can search in any way they please" problem is to create the kitchen sink procedure, which uses dynamic SQL to construct the where clause depending on the parameters provided. What this does is allow SQL Server to compile a separate plan for each combination of parameters, so that you don't have one massive query that tries to optimize for all scenarios but really can only optimize for very few. The basic approach would be:

CREATE PROCEDURE dbo.KitchenSink
  @param1 datatype = NULL,
  @param2 datatype = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table 
    WHERE 1 = 1'

    + CASE WHEN @param1 IS NULL THEN N'' ELSE
        N' AND column1 = @param1' END

    + CASE WHEN @param2 IS NULL THEN N'' ELSE
        N' AND column2 = @param2' END

    ...
  ;

  EXEC sys.sp_executesql @sql, N'@param1 datatype, @param2 datatype, ...',
         @param1, @param2, ...;
END
GO

This will provide you with the opportunity to make the best use of any indexes that have key columns that align with combinations of supplied parameters. You will have to concede that some queries will just not perform well, but this should help minimize those cases.

Additional caveats:

• You can use OPTION (RECOMPILE) inside the dynamic SQL to thwart parameter sniffing (for example, even when only lastname is supplied, the dynamic SQL alone won't help you generate the best plan every time, since a search for LIKE '%x%' will require a different plan than LIKE '[a-m]%').
• You should use the optimize for ad hoc workloads setting (see here and here), so that SQL Server doesn't waste valuable plan cache space on potentially complex and large plans that are only used once.

Answer 2

If your UI forces you to perform LIKE %xx% operations against Numeric Fields, then this will allow your query to perform SEEK operations on your indexes by jumping to a point that discounts unwanted records.

(Aaron's code is spot on, but we had something similar in our production environment and LIKE %% was causing SCANS, making the following modifications improved performance massively by converting the SCANS to SEEKS)

--Variable Recieved by stored procedure
DECLARE @customerid INT= 1000

--Startpoint enabling SEEK operations against Index
DECLARE @customer_start_point INT 

SET @customer_start_point = ISNULL(@customerid,0)


DECLARE @STR = 
'
SELECT
    *
FROM
    dbo.table
WHERE
    --The query will perform a SEEK and skip the first 1000 records
    CustomerID >= ' + @customer_start_point


+ CASE WHEN @customerid IS NULL THEN ''
        ELSE ' AND CAST(CustomerID AS VARCHAR(20)) LIKE ''%' + CAST(@customerid AS VARCHAR(20)) + '%'''  
        END