CODEWITHSUNDEEP
c#performancelistlinq
Mauricio Florez
Mauricio Florez

Reputation: 1132

How to index query List with Linq?

I have a List of DTO's with 8 fields, the List have 477.000 records loaded fron a XML file, when I filtered items on this list the CPU consumed 25% in one core, I think this behavior is because the List is not indexed, is it posible index that list?

DTO

public class PriceDto
{
    public string Filter { get; set; }
    public string Material { get; set; }
    public string Value { get; set; }
    public string Currency { get; set; }
    public string Max { get; set; }
    public string Min { get; set; }
    public string PorcentValue { get; set; }
    public string PromotionPrice { get; set; }
    public string ConditionClass { get; set; }
    public string Vat { get; set; }
}

Linq Query

Price = AllPrices.FirstOrDefault(x => x.Filter == FilterId.ConsecutiveFilter.Trim() &&
x.ConditionClass == accessSequenceItem.PriceCondition &&
x.Material == CodeMaterial);

Upvotes: 0

Views: 234

Answers (1)

NetMage
NetMage

Reputation: 26917

You can create a Lookup or a Dictionary<Lookup> or even a Dictionary<Dictionary<Lookup>> with LINQ and then use that for your queries. This trades space for time, but the improvement will really depend on the distribution of values in the different fields. With 500,000 in a random sample data set of 100 filters, 100 materials and 25 conditions, indexing on filter and material produces the fastest result for 2000 random queries, about 33 times faster than the plain query. 2000 queries for non-existent data was from 60 to 800 times faster with the lookup.

Here is the code to create the double index structure:

var filterMaterialMap = AllPrices.GroupBy(ap => ap.Filter).ToDictionary(apfg => apfg.Key, apfg => apfg.ToLookup(ap => ap.Material));

You run a query like so:

PriceDto Price = null;
if (filterMaterialMap.TryGetValue(FilterId.ConsecutiveFilter.Trim(), out var matDict))
    Price = matDict[CodeMaterial].FirstOrDefault(ap => ap.ConditionClass == accessSequenceItem.PriceCondition);

The other cases are a single field index:

var filterMap = AllPrices.ToLookup(ap => ap.Filter);
var Price = filterMap[FilterId.ConsecutiveFilter.Trim()].FirstOrDefault(ap => ap.Material == CodeMaterial && ap.ConditionClass == accessSequenceItem.PriceCondition);

and all three fields indexed:

var filterMaterialConditionMap = AllPrices.GroupBy(ap => ap.Filter)
                                          .ToDictionary(apfg => apfg.Key, apfg => apfg.GroupBy(ap => ap.Material)
                                                                                      .ToDictionary(apfmg => apfmg.Key, apfmg => apfmg.ToLookup(ap => ap.ConditionClass)));
PriceDto Price = null;
if (filterMaterialConditionMap.TryGetValue(FilterId.ConsecutiveFilter.Trim(), out var matDict))
    if (matDict.TryGetValue(CodeMaterial, out var condDict))
        Price = condDict[accessSequenceItem.PriceCondition].FirstOrDefault();

Upvotes: 1

Related Questions