4
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Have you edited the code before posting? It looks like that because there is so much free space... \$\endgroup\$ – t3chb0t Jun 18 '18 at 11:41
  • \$\begingroup\$ @t3chb0t adjusted it sorry about that \$\endgroup\$ – David B Jun 18 '18 at 11:56
  • \$\begingroup\$ But is the code complete of have you removed anything? \$\endgroup\$ – t3chb0t Jun 18 '18 at 12:01
  • 2
    \$\begingroup\$ Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$ – Toby Speight Jun 18 '18 at 12:12
  • \$\begingroup\$ @TobySpeight no i have not removed anything this is the standard code i am usinbg \$\endgroup\$ – David B Jun 18 '18 at 12:21
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Not sure but I think with deferred execution codesForThisItem will still be evaluated 12 times. First is not the same as FirstOrDefault. \$\endgroup\$ – paparazzo Jun 18 '18 at 15:02
  • \$\begingroup\$ @paparazzo list: you're right, fixed, tnx! First(): exactly, in OP is using FirstOrDefault() but if returned value is null then it'll throw NullReferenceException, better then First() with a slightly more informative InvalidOperationException. \$\endgroup\$ – Adriano Repetti Jun 18 '18 at 15:39
1
\$\begingroup\$

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();
    }
}
\$\endgroup\$
  • \$\begingroup\$ Thanks that has helped allot i am still dealing with 38 thousand records after talking to customer for restrictions what is the best method for reporting progress back to the user I am using a console app for the export \$\endgroup\$ – David B Jun 18 '18 at 14:55
0
\$\begingroup\$

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(),
        }
    }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.