How should make faster SQL Server filtering procedure with many parameters

Question

At the moment I am trying to find any source for searching in big tables I have good modeled tables 1 main table for users and a few tables for features like gender, address, last purchase, reviewed products, etc.

• EF was really slow when combining and filtering this, so I decided to use the stored procedure and calling with dapper.
• I want to filter this data which almost 5.5GB (470k rows and it is going to bigger, 31 columns, 7 tables, each table +15 column).
• I have 5 different filters it has to be fast. Because The procedure is responding estimated 1 min

The query should work with dynamic parameters

I have to do something like this I tried some different methods but still is slow. I have 5 filters and 1 date declare. Users can send 5 or 4 or 3 filters or nothing.

1. method I have tried if the parameter is null it's not doing anything but parameter is not null than it is filtering, but I read from a blog using 'or' is really reducing performance

    where (SaOr.InsertDate between ISNULL(@StartDate,'1900-01-01') and ISNULL(DATEADD(DAY, 1, @DueDate),@TOMORROW))
            and (@ProductName is null or  SaOrPr.Name like '%' + @ProductName + '%')
            and (@PaymentType is null or LEN(@PaymentType)> LEN(REPLACE(@PaymentType,PaymentMethodId,'')) )
            and (@Channel is null or LEN(@Channel)> LEN(REPLACE(@Channel,SaOr.ChannelId,'')))
            and (@SalesType is null or  LEN(@SalesType)> LEN(REPLACE(@SalesType,SalesOrderTypeId,'')) )
            and (@SalesStatus is null or  LEN(@SalesStatus)> LEN(REPLACE(@SalesStatus,StatusId,''))  )

2. method I have tried, without 'or' but it was slower than 1.

    where (SaOr.InsertDate between ISNULL(@StartDate,'1920-01-01') and ISNULL(DATEADD(DAY, 1, @DueDate),@TOMORROW)) 
      
            AND (SELECT CHARINDEX(ISNULL(ISNULL(@ProductName,SaOrPr.[Name]),' '),ISNULL(SaOrPr.[Name],' '))) >0
            AND (SELECT CHARINDEX(ISNULL(CAST(PaymentMethodId AS VARCHAR(38)),' '),ISNULL(ISNULL(@PaymentType,PaymentMethodId),' '))) >0
             AND (SELECT CHARINDEX(ISNULL(CAST(SaOr.ChannelId AS VARCHAR(38)),' '),ISNULL(ISNULL(@Channel,SaOr.ChannelId),' '))) >0
              AND (SELECT CHARINDEX(ISNULL(CAST(SalesOrderTypeId AS VARCHAR(38)),' '),ISNULL(ISNULL(@SalesType,SalesOrderTypeId),' '))) >0
               AND (SELECT CHARINDEX(ISNULL(CAST(StatusId AS VARCHAR(38)),' '),ISNULL(ISNULL(@SalesStatus,StatusId),' '))) >0

3. method is using dynamic query

   declare @query varchar(max)= 'insert into #TmpResult select
   some fields FROM #tmpSales SaOr

    where ( FilteredCount between   @pagination  and   @pagination + @PageSize - 1) '
   
    + CASE WHEN @PaymentType IS NOT NULL THEN
    ' AND LEN(@PaymentType)> LEN(REPLACE(@PaymentType,CONVERT(varchar(38),SaOr.PaymentMethodId),''''))  ' ELSE '' END
   
    + CASE WHEN @Channel IS NOT NULL THEN
    ' AND LEN(@Channel)> LEN(REPLACE(@Channel,CONVERT(varchar(38),SaOr.ChannelId),''''))  ' ELSE '' END
   
    + CASE WHEN @SalesType IS NOT NULL THEN
    ' AND LEN(@SalesType)> LEN(REPLACE(@SalesType,CONVERT(varchar(38),SaOr.SalesOrderTypeId),''''))  ' ELSE '' END
   
    + CASE WHEN @SalesStatus IS NOT NULL THEN
    ' AND LEN(@SalesStatus)> LEN(REPLACE(@SalesStatus,CONVERT(varchar(38),SaOr.StatusId),''''))  ' ELSE '' END
   
    +    '  OPTION (RECOMPILE);';
   

Even though still is not fast enough, 3. method is answering in an estimated 15 sec. How should I do in seconds?

Answer 1

You need to do few steps.

1. Divide tables into 2 or 3 parts, Like Filter product and payment then insert into #Temptable and Apply a INNER Join with other tables Like Channel and get Filtered Data #Temptable2 THEN Join 2nd Temptable with Sales and SalesType
2. Columns for filter in where, Create Index on all columns.

If you get Data from all tables at once and apply filters that will filter millions of record at once. So if you divide tables then filter will apply on less record.