C# IEnumerable performance loading large data from SQL Server database

Question

I am using C# ienumerable to load a large data from SQL server 2019 database but getting problems with the data loading. I want to find a way to improve the performance. Below is a sample of my code.

private IEnumerable<Students>GetAllStudents(int studentId) 
{     
return _objectProviders.Where(x =>x.Student.StudId == studentId  				  
&& !x.InActive); 
} 

Answer 1

This can depend on how big each Students object is. If it's big then I would avoid pulling the entire entity out into memory. This could be as trivial as adding a .Select() after your .Where() to map out only the properties you need, or if the query is pulling out all the associated entities for each Students then depending on your data provider, you could look at ensuring that they're not also pulled into memory.

A good place to start would be to open SQL Profiler and have a look at the query created by your app. Make sure it's not generating an inefficient query & that it's taking advantage of the indices you have in place.

You can even grab the query and run it in SSMS to see its execution plan, in case you're missing an index.

Answer 2

Very less informations.

You have a filter (WHERE) in your Linq query, creating an index meeting the filter would help.