Disable initial order search

Member since: 2012

In Commerce Manager under Order Management there is a page for search carts and purchaseorders and when you load that page it will do a blank search for all purchaseorders.

This really is completly unnecessary i can't see when someone wants to do a blank search and it takes a very long time so the administrator have to wait for it to load.

For some customers it even risks running so long that the administrator gets a timeout and the page fails to load completely.

#181875 Sep 01, 2017 13:12
  • Member since: 2011

    I'm pretty sure it only loads first 20 orders. It should not be that slow. 

    #181886 Sep 02, 2017 9:56
  • Member since: 2012

    Hello Quan

    Well you are correct that it only loads the first 20 orders, but that is far from the whole truth.

    First of all a sql profiler trace in my development environment actually reveals two database queries:

    declare @p8 int
    set @p8=476
    exec ecf_Search_PurchaseOrder @SQLClause=N'(1=1)',@MetaSQLClause=N'',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=10,@RecordCount=@p8 output
    select @p8
    declare @p8 int
    set @p8=476
    exec ecf_Search_PurchaseOrder @SQLClause=N'(1=1)',@MetaSQLClause=N'',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder,PurchaseOrder',@StartingRec=0,@NumRecords=20,@RecordCount=@p8 output
    select @p8

    The output parameter that returns 476 here i found quite revealing, 476 is the total number of purchaseorders in my environment, which brings us to the second point.

    To prepare the list for pagination the stored procedure goes through and counts the total number of orders in the system (for one of our production environments this is greater than five million).

    The actual sql statement that takes the time is:

    with OrderedResults as (SELECT count([OrderGroup].OrderGroupId) OVER() TotalRecords, [OrderGroup].OrderGroupId, Rank, ROW_NUMBER() OVER(ORDER BY OrderGroupId DESC) RowNumber FROM [OrderGroup] OrderGroup INNER JOIN (select distinct U.[KEY], U.Rank from (select 100 as 'Rank', META.ObjectId as 'Key', * from OrderGroup_PurchaseOrder META) U) META ON OrderGroup.[OrderGroupId] = META.[KEY]  WHERE 1=1 AND ((1=1))) INSERT INTO @Page_temp (TotalRecords, OrderGroupId) SELECT top(20) TotalRecords, OrderGroupId FROM OrderedResults WHERE RowNumber > 0

    So i assure you, it is that slow. :)

    Best Regards

    Erik Norberg

    #181907 Sep 04, 2017 16:46
  • Member since: 2011

    Interesting. I happen to have one of your databases - I'll look into it.

    #181909 Sep 04, 2017 17:24
  • Member since: 2011

    With your query I was able to run it in 14-42 minutes (depends on how "warm" the cache is). Note that that big database is on my HDD, so it's supposed to be slow (man you should really hear my HDD at work :), I feel sorry for it - the database is just too big to put on my SSD )

    Now this in an interesting part - with some optimizations I managed to get it down to 6-10s. Again, on HDD, so it should be much faster on a decent SSD. 

    This is preliminary result, I will need to do more testing to ensure my "optimizations" are actual correct. However if things go well then this will be incorporated in future releases.

    Thanks for bringing this into our attention. 

    #181926 Sep 05, 2017 15:15
  • Member since: 2012

    Thanks Quan

    I am looking forward to see it's speed in a future version :)

    #181928 Sep 05, 2017 15:57
  • Member since: 2011

    Just to follow up - the fix is available in Commerce 11.2.3 (released), and then slightly improved in 11.2.5 (yet to be released).

    I had a chance to test the improved version on another customer site - not yours, and I'm happy to say it makes the difference of day and night :)

    #182757 Sep 28, 2017 15:34
  • Member since: 2012

    Great, glad we could help fix the issue for more customers even if they didn't report it themselves. cool

    #182762 Sep 28, 2017 15:58