C#•5mo ago

Performance question

Hey guys, I have an web app and I tried to optimize the performance of one report. One stuff that seems unefficient to me is this one:

var searchedResults = query.Count();
if (index.HasValue && pageSize.HasValue)
{
    query = query.Skip(index.Value).Take(pageSize.Value);
}

var result = query.ToList();

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = query.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = query.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = query.Sum(q => q.QuarantinedOnSite)
};

var searchedResults = query.Count();
if (index.HasValue && pageSize.HasValue)
{
    query = query.Skip(index.Value).Take(pageSize.Value);
}

var result = query.ToList();

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = query.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = query.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = query.Sum(q => q.QuarantinedOnSite)
};

If I understand it correctly, it'll execute the whole query 4 (5 if I count also counting but guess it's necessary) times so I tried to do optimize it to do everything in one step like this:

var result = new List<SimsSiteInventoryReportData>();
    var total = new SimsSiteInventoryReportData();
    
    foreach (var item in query)
    {
        total.QuantityOnSite += item.QuantityOnSite;
        total.QuantityDispatchedToSite += item.QuantityDispatchedToSite;
        total.QuarantinedOnSite += item.QuarantinedOnSite;
        result.Add(item);
    };

var result = new List<SimsSiteInventoryReportData>();
    var total = new SimsSiteInventoryReportData();
    
    foreach (var item in query)
    {
        total.QuantityOnSite += item.QuantityOnSite;
        total.QuantityDispatchedToSite += item.QuantityDispatchedToSite;
        total.QuarantinedOnSite += item.QuarantinedOnSite;
        result.Add(item);
    };

but according the Network in Chrome this optimalization is even a bit slower and I cant understand why. Any explanation ? Thanks

18 Replies

Angius•5mo ago

5 times, yes. .Count(), .ToList(), .Sum() x 3 None of them being async either, so the pinnacle of unoptimised I'd definitely check what the performance actually is As in, what takes the most time, how long is the database round trip, how long the endpoint takes to execute, how much of that is network latency, and so on

CryyOP•5mo ago

Thank you. I tried it repeatedly and it's slower in same condition (network, hw, etc) and it really seems it consistently slower. It's small difference but I'd expect that my optimalization supposed to be faster. There are enother issues with the code and query like you said but cuurently I just want to understand this part

MarkPflug•5mo ago

What are you doing with result? ToList will bring the entire query result back to your application. But, for what? If you need result, why not just sum it in app memory, rather than summing query and avoid three roundtrips to db?

Angius•5mo ago

I mean, that's what the latter version does And yet it is, apparently, slower Ah, no, wait, what is query in the latter example?

CryyOP•5mo ago

That came to my mind to and I changed it like that "var result = query.ToList();

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = result.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = result.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = result.Sum(q => q.QuarantinedOnSite)
};

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = result.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = result.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = result.Sum(q => q.QuarantinedOnSite)
};

and it is faster but I understand why so I did not mentioned in 🙂

Angius•5mo ago

Because if you're looping over an IQueryable, then each loop iteration is a new db call

MarkPflug•5mo ago

But that doesn't answer why result is needed in the first example.

CryyOP•5mo ago

not sure I understand. Query is IQueryable and We are returning list from our repositories ... well, we returing something like that:

return new GetListResult<SimsSiteInventoryReportData>
{
    ResultItems = result,
    TotalItemsCount = searchedResults,
    Totals = total
};

return new GetListResult<SimsSiteInventoryReportData>
{
    ResultItems = result,
    TotalItemsCount = searchedResults,
    Totals = total
};

Angius•5mo ago

So, yeah, each loop over iquerable is another db call Resolve the query first, then loop over that Basically a mixture of the two versions

CryyOP•5mo ago

Yeah it's IQueryable andyeah maybe that's the reason. But ... ToList() handles it differently ?

Angius•5mo ago

.ToList() resolves the query in a single db call And returns an in-memory list of items

CryyOP•5mo ago

ok, so now I understand why it's slower. Not it makes sense. Thank you

Angius•5mo ago

Then, summing stuff in memory is easy and fast

CryyOP•5mo ago

I expeccted it does something similar like foreach ... I dont know how it's implemented to be honest

Angius•5mo ago

I'd use the original code, but do the summing on result not on query

var searchedResults = query.Count();
if (index.HasValue && pageSize.HasValue)
{
    query = query.Skip(index.Value).Take(pageSize.Value);
}

var result = query.ToList();

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = result.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = result.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = result.Sum(q => q.QuarantinedOnSite)
};

var searchedResults = query.Count();
if (index.HasValue && pageSize.HasValue)
{
    query = query.Skip(index.Value).Take(pageSize.Value);
}

var result = query.ToList();

var total = new SimsSiteInventoryReportData
{
    QuantityOnSite = result.Sum(q => q.QuantityOnSite),
    QuantityDispatchedToSite = result.Sum(q => q.QuantityDispatchedToSite),
    QuarantinedOnSite = result.Sum(q => q.QuarantinedOnSite)
};

CryyOP•5mo ago

i did it before and it's faster, thanks yeah. exactly.

Anton•5mo ago

also don't test it with the debugger enabled

CryyOP•5mo ago

I just wanted to know why the first example wasnt and now I got my answer ... so thanks again. I did not. I just look to network tab to see how long the request takes thanks guys

Gaming

Programming

Performance question