We have a large application having billions of records stored in the database and we have a global search page that searches items from a large DB repository.
We have 120 dynamic filters and 150 fields and user can select anything as he/she wants.
Each dynamic field has also multiple options like Is, is not, starts with, ends with, contains, does not contains, is blank, is not blank. Users can select any one from these options.
Overall everything is dynamic based on the user’s choice.
Our database is MS SQL Server and the front-end application is in .net core in MVC style.
We have made a store procedure that executes a query for this but it contains 40 joins and multiple “case when” statements, stuff function that gives results but taking too much time.
We need performance optimization for this requirement and the client is asking urgent.
Can anybody share the ideas what is the best way to fulfill this requirement and get results quickly?
Top comments (0)