Painfully Slow Operation

eddieangel

I am trying to root out why this particular linq query is so expensive from a time standpoint, assuming it is the linq query that is causing it.

public static List GetDocs()
{
List output;
using (context = contextLogic())
{
output = (from d in context.DocumentTypes
select d).AsEnumerable().Select (x => new DocumentType
{
Id = x.iD,
Name = x.Name,
FlagA = x.FlagA,
IsActive = x.IsActive,
UpdateDate = x.UpdateDate,
CategoryName = x.DocumentCategory.Name,
Associations = string.Join(",", x.DocumentAssociations.Select(g => g.ADocID.ToString()))
}).ToList();
}
return output;
}

I tried flipping things around quite a bit. This was initially an observablecollection but it is a list right now as I try to tune it in different ways. Right now it is taking about 12 seconds to retrieve data from a table of 350 records. If I take out the CategoryName and the associations it times down to about six seconds, but that is still ridiculously slow.

Matt T Heffron

Is the context local or is this on a server? Try ruling out the new DocumentType by changing the .Select(...).ToList() to just be .Count() (change the assignment variable appropriately, it is just for testing...). The .AsEnumerable() is totally unnecessary. This is equivalent to what you have:

public static List<DocumentType> GetDocs()
{
  using (var context = contextLogic())
  {
    return (from x in context.DocumentTypes
              select new DocumentType {
                Id = x.Id,
                Name = x.Name,
                FlagA = x.FlagA,
                IsActive = x.IsActive,
                UpdateDate = x.UpdateDate,
                CategoryName = x.DocumentCategory.Name,
                Associations = string.Join(",", x.DocumentAssociations.Select(g => g.ADocID.ToString()))
              }).ToList();
  }
}

eddieangel

Unfortunately, the AsEnumerable is necessary as L2E does not recognize string.Join. So you have to present it as an IEnumerable in order to join the Doc strings. Regardless, I retooled the process to use a stored procedure but it is still a nightmare. The data access is fast, the stored procedure fills a list in less than a second, but assigning those items to the DocumentType model takes four or five seconds more. It looks like it is the model itself. I am wondering if the implementation of INPC on the model is not causing a lot of slow down. Each time a new document type is added to the list it fires the OnPropertyChanged event for every single property. Not sure that is the problem, but it is a lot of events.

Dave Kreskowiak

I had something a bit similar to this. I just put together SQL in the database to build the stuff I was trying to do in the LINQ query. This also involves changing your model so that it returns this field as a database calculated field so EF doesn't try to write to it.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

eddieangel

I ended up moving all the data access logic to a SQL SP using STUFF for that very reason. The data access from SQL is a snap, but loading the data into the model is time consuming. Frankly, I think that the nature of the design of the WPF screen is the issue. Each of the items is loaded into a list view and has two states, one editable state and one that is not editable. Any time you make a change to the property in the editable mode you have to fire the NotifyPropertyChanged event so that the UI will update it in the non-editable form. As a result, every single property of every row fires an OnPropertyChanged event during the initial load. I think this is likely the problem. I just don't know how to work around it. If I could avoid that initial event firing I think that things would be much quicker.