List mapping of large data set from file

Question

I have a list of items 116 thousand and my mapping function is taking quite some time to process. I am using the file helpers library to write the CSV to disk but its the for each that is taking forever to complete any ideas how I might optimize this.

    private List<ItemsExport> MapTradeItems(List<TradeItems> tradeItem)
    {
       var retList = new List<ItemsExport>();
        try
        {               
            List<StandardLookUpList > _AnalsisCodes = GetAnayalsisCodesForExportCode();                       
            foreach (var item in tradeItem)
            {
                retList.Add(new TradeItemsExport()
                {
                    ItemCode = item.ItemCode,
                    BarCode = item.BarCode,
                    Description = item.Description,
                    SupplierCode = item.SupplierCode,
                    SupplierStockCode = item.SupplierStockCode,
                    Product_Group_Code = "",
                    Product_Group_Desc = "",
                    SportCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Sport).FirstOrDefault().code.ToString(),
                    SportDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Sport).FirstOrDefault().description.ToString(),
                    GenderCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Gender).FirstOrDefault().code.ToString(),
                    GenderDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Gender).FirstOrDefault().description.ToString(),
                    ColourCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Colour).FirstOrDefault().code.ToString(),
                    ColourDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Colour).FirstOrDefault().description.ToString(),
                    SizeCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Size).FirstOrDefault().code.ToString(),
                    SizeDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Size).FirstOrDefault().description.ToString(),
                    CategoryCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Category).FirstOrDefault().code.ToString(),
                    CategoryDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.Category).FirstOrDefault().description.ToString(),
                    SearchCategoryCode = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.SubCategory).FirstOrDefault().code.ToString(),
                    SeearchCategoryDesc = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode && w.code == Constants.SubCategory).FirstOrDefault().description.ToString(),
                    Vat = item.Vat,
                    GrossWeight = item.Weight,
                    CommodityCode = item.CommodityCode,
                    price_exVAT = item.price_exVAT,
                    price_incVAT = item.price_incVAT,
                    currentprice_exVAT = item.currentprice_exVAT,
                    currentprice_incVAT = item.currentprice_incVAT,
                    creation_date = item.creation_date,
                    Inactive_date = item.Inactive_date,
                    status = 1
                });
            }
        }catch (Exception ex)
        {


        }

        return retList;

    }

This function gets the analysis codes and adds them in memory list this bit is fast.

 private List<StandardLookUpList> GetAnayalsisCodesForExportCode()
 {
       List<StockItemSearchCategoryDescriptions_Result> _list = new List<StockItemSearchCategoryDescriptions_Result>();
        var retList = new List<StandardLookUpList>();
        using (var db = new liveEntities1())
        {
            _list = db.Database.SqlQuery<StockItemSearchCategoryDescriptions_Result>(StockItemSearchCategoryDescriptions").ToList();

            foreach (var item in _list)
            {
                retList.Add(new StandardLookUpList()
                {
                    ItemCode = item.Code,
                    code = item.ProductGroupCategory,
                    description = item.ProductGroupValue

                });
            }               
        }
        return retList;
 }

Trade Items is the stock table which is LINKED via item code and has 116 thosuand items analysis codes are also linked to the stock table which has the same 116 thousand items as each line can have up to 7 analysis codes.

Answer 1

I'll address only one single bit about performance: you're searching inside _AnalsisCodes (which is 116k items) TWELVE times. You may (and probably should) do it with a JOIN in your DBE but at first search only once and create a sublist:

var codesForThisItem = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode).ToList();
var sport = codesForThisItem.First(x => x.code == Constants.Sport);
var gender = codesForThisItem.First(x => x.code == Constants.Gender);
// ...

retList.Add(...
    SportCode = sport.code;
    SportDesc = sport.description
    GenderCode = gender.code,
    GenderDesc = gender.desc,

In this way we search inside the entire 116k collection only once per item and the subsearches are performed locally in tiny collections. You may use something better than a plain List<T> (for example an Hashtable where the hash is calculated on ItemCode) but for now let's stick to it. Note that I'm using First() instead of FirstOrDefault(), reading a property from null throws an exception then it does not make sense.

There is much more you can do (for example you may return results without creating a new list):

yield return new FuelImportSage.Classes.Export.TradeItemsExport()
{
    ItemCode = item.ItemCode,
    // ...
}

Also, inside GetAnayalsisCodesForExportCode() you do not materialize the full list in memory calling ToList(): you create a HUGE list you discard few milliseconds later. See Jesse's answer about that part.

Answer 2

Couple of quick optimizations in your "fast" method:

1. List<StockItemSearchCategoryDescriptions_Result> _list = new List<StockItemSearchCategoryDescriptions_Result>();: remove the initializer as it's being thrown away and replaced a couple lines after: List<StockItemSearchCategoryDescriptions_Result> _list.

2. Reduce/eliminate reallocations on the very next line (var retList = new List<StandardLookUpList>();) by moving it to just before the foreach and specifying the known capacity in the constructor: var retList = new List<StandardLookUpList>(_list.Count);

Full code:

private List<StandardLookUpList> GetAnayalsisCodesForExportCode()
 {
       List<StockItemSearchCategoryDescriptions_Result> _list;
        using (var db = new podium_liveEntities1())
        {
            _list = db.Database.SqlQuery<StockItemSearchCategoryDescriptions_Result>("StockItemSearchCategoryDescriptions").ToList();
            var retList = new List<StandardLookUpList>(_list.Count);

            foreach (var item in _list)
            {
                retList.Add(new StandardLookUpList()
                {
                    ItemCode = item.Code,
                    code = item.ProductGroupCategory,
                    description = item.ProductGroupValue

                });
            }               
            return retList;
        }
 }

Of course, now that I'm looking at it, the whole thing can be replaced with a very fast, very succinct LINQ statement:

private List<StandardLookUpList> GetAnayalsisCodesForExportCode()
{
    using (var db = new podium_liveEntities1())
    {
        return db.Database
            .SqlQuery<StockItemSearchCategoryDescriptions_Result>("StockItemSearchCategoryDescriptions")
            .Select(item => new StandardLookUpList
            {
                ItemCode = item.Code,
                code = item.ProductGroupCategory,
                description = item.ProductGroupValue
            })
            .ToList();
    }
}

Answer 3

You are doing search Where(w => w.ItemCode == item.ItemCode 12 times. Capture it in a List. If you don't materialize to a List I think you get deferred execution and it would still get evaluated 12 times.

I am not sure analysisSport will not be evaluated for both Code and Description. I am pretty sure FirstOrDefault() is an immediate and single analysis.

static void Parse(List<TradeItem> tradeItems)
{
    List<StandardLookUpList> analsisCodes = new List<StandardLookUpList>();

    foreach (var item in tradeItems)
    {
        List<StandardLookUpList> lookupItem = analsisCodes.Where(w => w.ItemCode == item.ItemCode).ToList();
        StandardLookUpList analysisSport = lookupItem.Where(w => w.Code == Constants.Sport).FirstOrDefault();
        retList.Add(new TradeItemsExport()
        {
            ItemCode = item.ItemCode,
            SportCode = analysisSport.code.ToString(),
        }
    }
}