Timeouts when searching for orders

[Damnias3]

Which component is this issue related to?

Umbraco Commerce (Core)

Which Umbraco Commerce version are you using? (Please write the exact version, example: 10.1.0)

v13.7.2

Bug summary

Normally, when opening Umbraco Commerce without any filters, everything works fine and the orders are displayed correctly.

However, when we apply a filter on orders based on an order line property, we experience timeouts.
If we add a “Placed On or After” filter, the issue doesn’t occur — but that likely helps only because it reduces the dataset size.

The timeout seems to occur after about 30 seconds, and after that, we need to clear the cache before the orders become visible again.

We have tried adding indexes to the UmbracoCommerceOrder and UmbracoCommerceOrderline tables, as suggested by Azure. This helped temporarily (for about a day), but then the timeouts returned.

We have 197.000 rows in our orders table and 132.000 rows in our orderLine table, doesn't know if that has a impact.

Specifics

No response

Steps to reproduce

Filter after orderline properties, in Umbraco Commerce -> Orders

Expected result / actual result

No timeouts

Dependencies

No response

[acoumb]

Hi @Damnias3 ,

Is the provided version - 13.7.2 the one of the CMS? Because it does not matches one from Commerce.
Could you tell me which version of Commerce you are using?

Thanks,
Adrian

[Damnias3]

Hi.

Sorry for the confusing.

Our CMS is running 13.7.2, and the installed Commerce package is 13.2.2

Best regards

[acoumb]

@Damnias3 I was not able yet to replicate this, but could you tell me if any exception is logged on that 500 error request?

[Damnias3]

Hi.

Hmm now i'm not getting the timeout.

Would it be possible to keep this issue open for a couple of days, i just want to check if the administrators of our commerce is experiencing the same as me.

[acoumb]

Sure thing, please keep us posted.

[Damnias3]

I finally have some more information.

The request that fails

/umbraco/backoffice/umbracocommerce/umbracocommerceorder/SearchOrders?storeId=ab778579-4a3c-4eb0-85dd-0180d71c847b&searchTerm=&pageNumber=1&pageSize=&orderBy=name&orderDirection=asc&orderlineProps=VariantLocation:Thisted&itemsPerPage=

Also have one where orderlineProps is orderlineProps=VariantLocation:Aarhus

After around 30 seconds we get a status code 500, and this message Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

And no logs, on our app service in azure, but maybe we just need to add some extra logs