Iteration over a linq to sql query is very slow.

Question

I have a view, AdvertView in my database, this view is a simple join between some tables (advert, customer, properties). Then I have a simple linq query to fetch all adverts for a customer:

public IEnumerable<AdvertView> GetAdvertForCustomerID(int customerID)
{
    var advertList = 
        from advert in _dbContext.AdvertViews
        where advert.Customer_ID.Equals(customerID)
        select advert;
    return advertList;
}

I then wish to map this to modelItems for my MVC application:

public List<AdvertModelItem> GetAdvertsByCustomer(int customerId)
{
    List<AdvertModelItem> lstAdverts = new List<AdvertModelItem>();
    List<AdvertView> adViews = _dataHandler.GetAdvertForCustomerID(customerId).ToList();
    foreach(AdvertView adView in adViews)
    {
        lstAdverts.Add(_advertMapper.MapToModelClass(adView));
    }
    return lstAdverts;
}

I was expecting to have some performance issues with the SQL, but the problem seems to be with the .ToList() function. I'm using ANTS performance profiler and it reports that the total runtime of the function is 1.400ms, and 850 of those is with the ToList(). So my question is, why does the tolist function take such a long time here?

Answer 1

GetAdvertForCustomerID doesn't return results, it returns a query.

ToList enumerates the query. The results are hydrated at that point. The database trip is happening within the ToList call.

You should get the generated sql (by using the SqlProfiler or the DataContext.Log property), and take it to query analyzer to examine the execution plan and IO (SET STATISTICS IO ON).

Answer 2

.ToList will execute the query, so that includes the entire roundtrip to the database, getting the data, and materializing the entity objects for every record returned... ...you're most likely spending most of that time db-side, so take a closer look at the execution plan for your view.