I have a query that takes a left join with a table that contains million of records and could double even in a future. It has started to take around 6-7 seconds to respond. I tried using the AsNoTracking but weirdly it increases the load time instead of improving.

var res = (from rd in db.RequiredData
    join rdm in db.RequiredDataMapper
        on new { t1 = rd.Id } equals new { t1 = rdm.Id } 
    into rd_rdm
    from rdm in rd_rdm.DefaultIfEmpty()
    where (rdm.Name == null || rdm.Name == nameof(Name))
        && (model.TypeId == 0 || rdm.Id == model.STypeId)
        && (ApplicationType.ToLower() != "QR".ToLower() ?
            rdm.ATypes.TypeName.ToLower() != "QR".ToLower() : 1 == 1)
                select rd)
                .OrderByDescending(s => s.date)
                .AsNoTracking();

Now, I have narrowed it down as much as I could and after going through articles I found out that AsNoTracking() would fast it up but it has increased it's load time even more. What other options I have?

RequiredDataMapper table is a bridge table to maintain many to many relationships between RequiredData and RequiredDataMapper.

CodePudding user response：

Here's what I would do if I were you. I'm assuming you're using SQL.

1 - I would get the raw version of the query like Select *

2 - I would run this on MSSQL and see if it takes really long.

3 - If it takes a long time, if it is not a problem to read dirty data, I would apply WITH(NOLOCK) It is a bit difficult to implement in Ef core. You have to consider it as Isolation level.

4 - I would look at the execution plan of the query. Where the data comes from is bottlenecked. Accordingly, I would switch to indexing operations. In general, the execution plan warns you if there is a missing index and gives an example script.

5 - If there is no problem with what I have counted so far, the indexes used while the query is coming may be fragmented. You can have the index rebuilt by Rebuild or Reorganize.