Performance Issues After Upgrade from v13 to v15

[marvingbh]

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)

15.3.8

Bug summary

After migrating from version 13 to 15, we're seeing significant user complaints regarding the commerce/orders pages. Upon checking in DevTools, we noticed that a simple search request is taking up to 9 seconds, and requests for carts are hitting 17 seconds.

This performance regression is severely impacting user experience. Screenshots from DevTools below:

cart:

orders:

Specifics

this was much faster in v13

Steps to reproduce

Open a orders page in V15

Expected result / actual result

work fast

Dependencies

No response

[mattbrailsford]

Is there anything in your log table suggesting any issues?
Is it slow with no filter applied?
Have you checked SQL Server for any slow running queries?
Are there any custom integrations that could be affecting the speed?

[marvingbh]

Is there anything in your log table suggesting any issues?
nothing there, users are also complaining about time out open orders to follow-up. like this:

Is it slow with no filter applied?
it is, first time to open without filter takes up to 9s to show something
Have you checked SQL Server for any slow running queries?
any query that i do in orders table is returning with miliseconds.
Are there any custom integrations that could be affecting the speed?
no custom integrations.

[mattbrailsford]

Are you able to reproduce it locally?
Are you able to run any profiling tools to see where the code is spending most time?

[marvingbh]

Our database is in azure, that show those queries as log running queries:

(@8 int,@9 uniqueidentifier,@11 int,@10 bigint)SELECT [t1].* FROM [umbracoCommerceOrderLineProperty] AS [t1] 
INNER JOIN (SELECT [id] FROM [umbracoCommerceOrder] AS [o] WHERE [deletedTimestamp] = @8 AND ([storeId] = @9) AND ([o].[finalizedDate] IS NOT NULL) ORDER BY [o].[finalizedDate] DESC, [o].[createDate] DESC OFFSET @10 ROWS FETCH NEXT @11 ROWS ONLY) AS [o] ON ([t1].[orderId] = [o].[id])

(@0 int,@1 uniqueidentifier,@3 int,@2 bigint)SELECT [t1].* FROM [umbracoCommerceOrderProperty] AS [t1] 
INNER JOIN (SELECT [id] FROM [umbracoCommerceOrder] AS [o] WHERE [deletedTimestamp] = @0 AND ([storeId] = @1) AND ([o].[finalizedDate] IS NOT NULL) ORDER BY [o].[finalizedDate] DESC, [o].[createDate] DESC OFFSET @2 ROWS FETCH NEXT @3 ROWS ONLY) AS [o] ON ([t1].[orderId] = [o].[id])

(@5 int,@6 uniqueidentifier,@8 int,@7 bigint)SELECT [t1].* FROM [umbracoCommerceOrderTransactionActivity] AS [t1] 
INNER JOIN (SELECT [id] FROM [umbracoCommerceOrder] AS [o] WHERE [deletedTimestamp] = @5 AND ([storeId] = @6) AND ([o].[finalizedDate] IS NOT NULL) ORDER BY [o].[finalizedDate] DESC, [o].[createDate] DESC OFFSET @7 ROWS FETCH NEXT @8 ROWS ONLY) AS [o] ON ([t1].[orderId] = [o].[id]) ORDER BY [t1].[dateTimeUtc]

I can backup the database restore it locally and try to profile it, what tools do you suggest that can give you a good insights?

[mattbrailsford]

I tend to use dotTrace but depends if you have Jetbrains licensing. Application insights might be able to help if you are using Azure.

RE slow queries, what time is it spending on those queries?

[marvingbh]

those queries are taking between 7 to 10 seconds.

[mattbrailsford]

Hmm, interesting, so then that would suggest it's a problem at the DB level. What DB plan are you on? Is Azure suggesting any indexes to add? Can you tell if there are indexes on all orderId and storeId properties?

[marvingbh]

it suggested a index, but did not help, using basic with 5 DTUs

[mattbrailsford]

Hmm, it is suggested to be on an S2 minimum really for Umbraco. That's the lowest we went for our performance testing. Could be worth seeing if upping the resources helps.

[marvingbh]

just update to standard with 10 DTUs, but not help, still slow.

[mattbrailsford]

Good to know

[marvingbh]

azure suggested to create this index:

`CREATE NONCLUSTERED INDEX [nci_msft_1_umbracoCommerceOrder_FA8E8162A4F62C9A45444BF6ACAD9C90] ON [dbo].
[umbracoCommerceOrder] ([deletedTimestamp], [storeId], [finalizedDate]) INCLUDE ([amountAuthorized], 
[baseCurrencyExchangeRate], [createDate], [orderStatusId], [paymentStatus], [refundedAmount]) WITH (ONLINE = ON)`

this seems help, it a litle bit faster for orders now, but carts are still low.

[mattbrailsford]

Do you have the relevant indexes on the orderId and storeId columns? Also, how many orders / carts are we talking?