Option to OrderContext.Current.FindPurchaseOrders? Timeout in ecf

London Dev Meetup Rescheduled! Due to unavoidable reasons, the event has been moved to 21st May. Speakers remain the same—any changes will be communicated. Seats are limited—register here to secure your spot!

AI OnAI Off

Home / Forums / Developers (Main Products) Forum / Customized Commerce 13 and earlier versions /

Option to OrderContext.Current.FindPurchaseOrders? Timeout in ecf_Search_ShoppingCart

Kristoffer Lindén

Vote:

Hi!

Some more timeout problems. Yesterday we have problems with the ecfVersion and today on a completely different site it is the ecf_Search_ShoppingCart

This query:

declare @p8 int
set @p8=1
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
select @p8

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
set @p8=1
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
select @p8

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/

Thanks!

/Kristoffer

#183619

Oct 18, 2017 12:29

Quan Mai

Vote:

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.

#183625

Oct 18, 2017 14:31

Kristoffer Lindén

Vote:

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?

Thanks!

/Kristoffer

#183628

Oct 18, 2017 14:52

Quan Mai

Vote:

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.

#183633

Edited, Oct 18, 2017 15:01

Kristoffer Lindén

Vote:

Ok, thanks.  I added the index and the query now runs in less than two seconds. We have problem with the Remove expired carts job in the DXC, or well we have problems with all our schedule tasks that runs for more than 30 minutes. The site restarts and the job ends up i "Job is running". I think that might because the we have performance issues on the site and therefor the job never finish before the site restarts, but that is anoter issue. /Kristoffer

#183634

Oct 18, 2017 15:17

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.