How to improve the performance of the Linq query?

Question

I have the consultation below

var f = (from l in fpcList
join fpp in FPP on new { l.fpId, l.etId } equals new { fpp.fpId, fpp.etId }
join fpcp in FPCP on fpp.fppId equals fpcp.fppId
select new { fpp.fppId, fpp.fpId, fpp.etId, fpcp.ecId }).ToList();

The fpcList list has a regular 24 items

The FPP table has more than 200,000 items

The FPCP Table has over 600,000 items

The consultation is slow, I am a beginner programmer and if anyone knows another way to do the same, I appreciate.

SOLUTION (10-07-2019)

Post because it can help other programmers! I made the query to the Database first and then applied the filters of the list. Performance has improved significantly.

var f= (from fpp in FPP
join fpcp in FPCP on fpp.fppId equals fpcp.fppId
select new { fpp.fppId , fpp.fpId, fpp.etId, fpcp.ecId}).AsEnumerable()
.Where(y => fpcList.Any(x => x.fpId == y.fpId && x.etId == y.etId ))
.Select(x => new { x.fppId, x.fpId, x.etId, x.ecId}).ToList();

Thank you all very much

Answer 1

There are several things that can be done for performance improvement there.

Examples:

1) Create indices in the database (if they do not exist) for fpId+ etId in the FPP and also for fppId in the FPCP . How to Create Index in sql server

2) Bring only the fields you will use before giving a Tolist.

3) If it is an MVC site and you are sending the data to a view, no use Tolist. Send the model without tolist to view and receive as a Ienumarable in the view

4) Also, if you are going to present the data on a website, use paging and not take all the data as you are doing. Take all the data leaves really slow. In this case, if using paging is more efficient do this through Process. Example of how to make pagination on Sql Server here

var f = (from l in fpcList
join fpp in FPP on new { l.fpId, l.etId } equals new { fpp.fpId, fpp.etId }
join fpcp in FPCP on fpp.fppId equals fpcp.fppId
select new { fpp.fppId, fpp.fpId, fpp.etId, fpcp.ecId })
.Select(x => x new {CampoA = x.CampoA, CampoB = x.CampoB}).ToList();

Answer 2

The problem is not with Linq itself, but with the way you are doing the query in the database.

If you are working with Entity, see output of your debug the SQL being generated and try to run it directly in the database and analyze the performance. Check in your database if the relations on new { l.fpId, l.etId } equals new { fpp.fpId, fpp.etId } and on fpp.fppId equals fpcp.fppId are well done (there is the Foreingkey index for these relations?).

Once done, consider even if you have the need to bring all this data at once (as commented by Cypherpotato), add more filters as a Date period for example.

I hope I’ve helped.