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.

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,''))  )

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

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?

your suggestion is not using procedures, preparing a query at the back end then execute, is it ? — John Sarikaya
– John Sarikaya, Commented Jan 26, 2021 at 8:35
Suggest you read about this type of kitchen sink report coding. But your second approach is far worse than the first. Casting everything to string and then using charindex is logically flawed and effective fatal to the optimizer since those expressions cannot use indexes at all. — SMor
– SMor, Commented Jan 26, 2021 at 12:23

Ehtsham Zafar · Accepted Answer · 2021-01-27 06:57:59Z

0

You need to do few steps.

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
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.

answered Jan 27, 2021 at 6:57

Ehtsham Zafar

9

Sign up to request clarification or add additional context in comments.

1 Comment

John Sarikaya Over a year ago

I used something like this. I used temp tables then #user for users(est. 30k rows), #sales for main features of sales(100k rows), #salesOrders for each product (470k rows). I used filters on #sales, achieved data then joined the other temp tables. why I used temp tables, I've read about the benefits of temp table indexing

Collectives™ on Stack Overflow

How should make faster SQL Server filtering procedure with many parameters

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related