0

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.

  • 2
    Indexing strategy depends heavily on how the business is actually using the DB. Discuss with stakeholders about what kind of queries are most common and provide basic indexing. Most if not all RDBMS can be queried about missing indexes and such. – vonPryz Jan 10 '17 at 12:53
7

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.
  • After you've built up some history with this query, you can check the plan cache to see which columns are the most frequently requested and plan an index strategy around that. sp_BlitzCache (one of the tools at firstresponderkit.org) can help with that. Also, I suggest you begin your dynamic SQL statement with a comment identifying the stored proc it came from, otherwise it may be difficult to isolate them in the plan cache. Just add /***** Stored Proc: dbo.KitchenSink ******/ or something like that to the beginning of the @sql. – Doug Lane Jan 10 '17 at 22:31
0

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
  • But @CustomerID is optional in the OP's case, and if it is included with a WHERE CustomerID = @CustomerID clause, or even a range, you should be able to get a seek with or without other LIKE clauses, which might not even be there (I don't even know that any of the columns are string-based). – Aaron Bertrand Jan 10 '17 at 18:02
  • Hi, The programmers of the UI allowed users to type in Partial Account Numbers and IDs, then the users would be presented with a possible shortlist of Customers. The %% on numeric fields was rendering indexes useless/ Sorry I should have elaborated more. – pacreely Jan 10 '17 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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