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

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

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

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. 

Thanks Quan

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

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

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