0
\$\begingroup\$

Context:

This code compute the data from table "ItemReturn" into "StatReturn".
It take about 1 700 000 ItemReturn on the first run. For 2minute, computation and database insert.

ItemReturn: (int)Itm_Id, (int)Itm_Item_Serial, (datetime)Itm_CDate, [...]
StatReturn : (int)Stat_id, Itm_Id, NbReturn, NbReturn_at30d, NbReturn_at60d, [...]

For every return, we need to know: How many time "this" item was return in different timeframe (30,60,90.. days).

An item is unique based on his Serial (Itm_Item_Serial).

Function:

This function take in input a list of a ItemReturn give as result the `StatReturn.

private List<StatReturn> ComputeReturnStat(IEnumerable<ItemReturn> todoReturn)
{
    var ttMSE = todoReturn  .GroupBy(x => x.Itm_Item_Serial)
                            .Select(grp =>
                                        new InfoReturn(grp.Key
                                        , grp.Select(x => new MseDate((DateTime)x.Itm_CDate, x.Itm_Id))
                                              .OrderBy(x => x.InterD)
                                              .ToArray()
                                        , grp.Count()
                                        )
                                    );

    var result = new List<StatReturn>();

    foreach (var mse in ttMSE)
    {
        var statReturn = new StatReturn();
        statReturn.SR_Compteur = 0;
        statReturn.SR_NbRetour = (byte)mse.NbRetour;
        statReturn.SR_NbRetour30J = 0;
        statReturn.SR_NbRetour60J = 0;
        statReturn.SR_NbRetour90J = 0;
        statReturn.SR_NbRetour120J = 0;
        statReturn.SR_NbRetour180J = 0;
        statReturn.SR_NbRetour365J = 0;

        if (mse.NbRetour == 1)
        {
            statReturn.SR_Compteur = mse.Items.First().MSE_key;
            result.Add(statReturn);
        }
        else
        {
            for (int i = 0; i < mse.NbRetour; i++)
            {
                statReturn = new StatReturn();
                statReturn.SR_NbRetour = (byte)mse.NbRetour;
                statReturn.SR_NbRetour30J = 0;
                statReturn.SR_NbRetour60J = 0;
                statReturn.SR_NbRetour90J = 0;
                statReturn.SR_NbRetour120J = 0;
                statReturn.SR_NbRetour180J = 0;
                statReturn.SR_NbRetour365J = 0;
                statReturn.SR_Compteur = mse.Items[i].MSE_key;

                for (int j = i - 1; j >= 0; j--)
                {
                    var delay = (mse.Items[i].InterD - mse.Items[j].InterD).Days;

                    if (delay <= 30)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour30J++;
                    }
                    else if (delay > 30 & delay <= 60)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour60J++;
                    }
                    else if (delay > 60 & delay <= 90)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour90J++;
                    }
                    else if (delay > 90 & delay <= 120)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour120J++;
                    }
                    else if (delay > 120 & delay <= 180)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour180J++;
                    }
                    else if (delay > 180 & delay <= 365)
                    {
                        statReturn.SR_NbRetour++;
                        statReturn.SR_NbRetour365J++;
                    }
                }
                result.Add(statReturn);
            }
        }
    };
    return result;
}

Additional information:

  • InfoReturn, is a custom class.

    int nser;         // Itm_Item_Serial
    MseDate[] items;  // list of Return id (Itm_Id) and Date
    int nbRetour;     // Total of return
    
  • There is a byte cast in code because database is in small in so Linq-to-SQL type is byte.

  • All comment have been delete, and all variable have been translated for the post.

  • A lot of column in database are nullable, so we set them to 0 by default.

\$\endgroup\$
1
\$\begingroup\$

first var var statReturn is only used in if (mse.NbRetour == 1)

delay > 30 and other > are redundant

change statReturn to default those values to 0

I think you could do this sorted output and not need the GroupBy if you are having performance issues. Or do it in TSQL.

\$\endgroup\$
  • \$\begingroup\$ The T-SQL solution is not that easy to comes up with. And for the no groupby part I dont see what is the structure of data you want to loop throught. \$\endgroup\$ – Drag and Drop Jul 27 '17 at 9:24
  • \$\begingroup\$ Groupby is here for "distinct", I just see no other way around. \$\endgroup\$ – Drag and Drop Jul 27 '17 at 9:53
  • \$\begingroup\$ Sorry that only used if (mse.NbRetour == 1) is not clear. I don't know how to be more clear. Sorry I could not be of more assistance. \$\endgroup\$ – paparazzo Jul 27 '17 at 10:31
  • \$\begingroup\$ No I don't see your point. If you think creating something you might not use is good code then have at it. Again, sorry I could not be of more assistance. \$\endgroup\$ – paparazzo Jul 27 '17 at 11:10
  • \$\begingroup\$ This point is now clear, you're right, I create an Object that will just be left over in some case. Don't be mad at me if I'm slow. We can't blame my English on this one. It was 100% my bad. I think I need to improve my communication skill to avoid this in the future. Thanks for your time and help you provided. \$\endgroup\$ – Drag and Drop Jul 27 '17 at 11:25

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.