Order return list slow


/Apps/Order/ReturnList.ascx is really slow because it reads all completed(and partially shipped) orders and then tries to figure one by one if they have returns.

From sql trace and reflected code:

1st - Find purchase order by status is called


declare @p11 int
set @p11=2247
exec ecf_Search_PurchaseOrder @ApplicationId='app-guid',@SQLClause=N'(Status = ''Completed'' OR Status = ''PartiallyShipped'')',@MetaSQLClause=N'',@FTSPhrase=N'',@AdvancedFTSPhrase=N'',@OrderBy=N'',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p11 output
select @p11

called from code in Mediachase.Commerce.Manager.Apps.Order.ReturnList GetDataSource method:

private DataTable GetDataSource()
      DataTable dataTable = new DataTable();
      dataTable.Locale = CultureInfo.InvariantCulture;
      dataTable.Columns.Add(new DataColumn("OrderGroupId", typeof (int)));
      dataTable.Columns.Add(new DataColumn("RMANumber", typeof (string)));
      dataTable.Columns.Add(new DataColumn("OrderFormId", typeof (int)));
      dataTable.Columns.Add(new DataColumn("ReturnType", typeof (string)));
      dataTable.Columns.Add(new DataColumn("CustomerName", typeof (string)));
      dataTable.Columns.Add(new DataColumn("CustomerId", typeof (string)));
      dataTable.Columns.Add(new DataColumn("Created", typeof (DateTime))
        DateTimeMode = DataSetDateTime.Local
      dataTable.Columns.Add(new DataColumn("TrackingNumber", typeof (string)));
      OrderContext current = OrderContext.Current;
      OrderStatus[] orderStatusArray = new OrderStatus[2]
      foreach (PurchaseOrder purchaseOrder in current.FindPurchaseOrdersByStatus(orderStatusArray))
        foreach (OrderForm returnForm in (StorageCollectionBase) purchaseOrder.ReturnOrderForms)
          if (ReturnFormStatusManager.IsAwaitingCompletable(returnForm))
            DataRow row = dataTable.NewRow();
            row["OrderGroupId"] = (object) purchaseOrder.OrderGroupId;
            row["RMANumber"] = (object) returnForm.RMANumber;
            row["OrderFormId"] = (object) returnForm.OrderFormId;
            row["ReturnType"] = (object) returnForm.ReturnType;
            row["CustomerName"] = (object) purchaseOrder.CustomerName;
            row["CustomerId"] = (object) purchaseOrder.CustomerId.ToString();
            row["Created"] = (object) returnForm.Created;
            row["TrackingNumber"] = (object) purchaseOrder.TrackingNumber;
      return dataTable;

2nd - And then for each row in that this is called:

exec [ecf_OrderGroupNote_ListByOrderGroupId] @OrderGroupId=xx

And that takes few minutes, in the end I get list of about twenty returns in list!
Does it have to be this slow?
Am I doing something wrong, could it be configuration or something like that, looking at reflected code looks like its set in stone - foreach loop?
Have newer version already improved this?
If not, are there plans to improve it?

version: Mediachase.ConsoleManager, Version=

Nov 19, 2014 14:08


In 7.11 we made a very similar improvement to the Pick Lists. I think this can be improved as well - I'll log a bug for this and we will fix it along with upcoming plan of performance tuning.



Nov 19, 2014 15:03

Just for your information, we made a improvement in Commerce 8.5 where the notes now loaded in batches and it no longer queries database 20 times to get notes of 20 orders. But the main issue with ReturnList is still on our doing list.



Dec 08, 2014 10:56
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.