Disable initial order search

Vote:
 

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
Vote:
 

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

#181886
Sep 02, 2017 9:56
Vote:
 

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
go
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
go

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
Vote:
 

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

#181909
Sep 04, 2017 17:24
Vote:
 

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
Vote:
 

Thanks Quan

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

#181928
Sep 05, 2017 15:57
Vote:
 

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
Vote:
 

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:57
This thread is locked and should be used for reference only.
* 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.