Some more timeout problems. Yesterday we have problems with the ecfVersion and today on a completely different site it is the ecf_Search_ShoppingCart
declare @p8 int
exec ecf_Search_ShoppingCart @SQLClause=N'',@MetaSQLClause=N'META.TrackingNumber LIKE ''T0053776''',@OrderBy=N'',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'ShoppingCart',@StartingRec=0,@NumRecords=1,@RecordCount=@p8 output
Takes 12 minutes to complete!
We are using Commerce 11.2.2 so maybe there are another way in this version to search for an order with a special tracking number?
We are also using the search function to find a Klarna order number saved in meta data.
declare @p8 int
exec ecf_Search_ShoppingCart @SQLClause=N'',@MetaSQLClause=N'META.KlarnaCheckoutOrder LIKE ''FZ8W13CATHEL7P4QBR2OK89RMX9''',@OrderBy=N'',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'ShoppingCart',@StartingRec=0,@NumRecords=1,@RecordCount=@p8 output
Is there another way to search for orders with certain meta data or maybe there is a join problem as the case was in this post:https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/10/database-timeout-on-productvariant-update/
It's a bit strange to me that you would have TrackingNumber on a ShoppingCart. Usually it should only be on a purchase order.
We have a non clustered index on OrderGroup_PurchaseOrder.TrackingNumber - because it's a "system" metafield column. It's up to you to add an index to your custom metafield columns.
Also the if you know the value for sure, use = instead of LIKE
Also in 11.2.3 we improved the order search performance, which might benefit your site.
I'm not sure why it is there since we have inherited the solution. I will try to add an index an upgrade Commerce.
By the way, I just ran this query:
select count(*) from OrderGroup_ShoppingCart
It gave me 10628989! That looks quite strange to me? Should rows be removed when they become purcahse orders? Can you "clean up" that table in some way?
It depends on your business, but if you have no explicit requirements to keep the carts, then I'd say the common practice is to remove the cart when the order is placed.
And yes, you can clean up that table by running the Remove expired carts scheduled job. - again, if you business doesn't want to keep the carts indefinitely
10628989 sounds a lot but I've seen a customer with 70M carts :) - yes, because they can.