I am currently using NonFactors/MVC6-Grid to represent data on my index page for a fairly basic CRUD app built with .NET Core 2.0 & MVC. I have a model with several fields, a few of which are calculated fields.
Sorting and Filtering with the MVC grid works really well, even with > 550,000 records. However, when I am trying to sort and filter on one of the calculated fields, there is a great decrease in performance (it takes about 45 seconds). This isn't the end of the world, but I would like to know if there is a way to increase the performance of calculated fields?
I am pretty sure that the performance drop is either due to the lack of an index on the calculated fields, or the fact that each record's calculated property is re-calculated and then sorted/filtered, or both.
If anyone has any insight on where the bottleneck could be and if there was a way I could increase performance, it would be greatly appreciated.
From SQL Documentation
So you may be able to set up an index...depends on the calculation.
Also depending on the version of SQL server you are using....you can mark the column as PERSISTED.
Sorting and Filtering on the SQL Server with indexes will be much faster than client side as previously suggested
You get the records from DB, filter based on the condition and do the calculation in C# code, then map it to your list of Dto and apply sorting. And finally, return the list of Dto to client-side code.