MsSQL Filtering Methods

Hello, today I want to talk about filtering. I researched for a while, how can I filter data, I used ef core for everything, as you know that was really slow :) I want to put some velocity for fetching this data. I decided to use the stored procedure after that I said why I am not using the same procedure to filter the data. Because sp is returning the result, I show all of them or showing after the filter process. If the filters are specified, I use the backend for filter otherwise I will filter the data on the frontend.

But the procedure took all this responsibility from its shoulders :)

I had one view, it returns all data about my customers.

Why did I need this view I have a customer table for customer general things and other tables for customer details and registrations like Addresses, buckets, subscriptions, etc.

So I created the view to fetch all of them in one query, I put some variables for filtering. If it's not null, it will be active otherwise passive.

Firstly I used a dynamic query. Why? You have 2 way to make dynamic this filter variable one is adding a where condition at the end of the query

where @username is null or username like '%' + @username + '%'

It will check is null or not every time, this will cause performance to lose

declare @sql varchar(max) = 'select and from and all other things........ where 1=1' + CASE WHEN @username is not null THEN' username like ''%'' + @username + ''%''' else '' END

It will create a query with just what you need you can run it.

NOT: If the procedure works with different variables you should use the end of the query OPTION(recompile). SP creates an execution plan for every work and uses this for the next execution to gain speed. But if you send different variables it will make it slower.