Counting the number of returning item for a TimeFrame

Question

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.

Answer 1

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.