Filtering big data in SQL Server 2012 SP 2, what is the best method

Question

I am using EF Core 2.2, I will update to 5 when I feel comfortable.

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

• EF was really slow about this, so I used dapper and calling a stored procedure.
• 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

I can't use something like this

select name from users where username like '%john%'

or

select name from users where charindex('john',username) > 0

It should be dynamic

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

1. method I have tried, and 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 then 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

question from:https://stackoverflow.com/questions/65896636/filtering-big-data-in-sql-server-2012-sp-2-what-is-the-best-method

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.