Backend Cart- & Orderlist slow and breaks with a lot of data

[MichaelNielsenDK]

When having a lot of data, the cart and orderlist in the backend becomes increasingly slow or break.

Here is an example when going to view the cart list, it takes almost 10 seconds
https://prnt.sc/WFCdm0SKu9cG

If I try and go to the last page in the list, which is pageNumber 8566, it fails
https://prnt.sc/Yy-2sIOekzgv

Umbraco 10.4.2
Vendr v3.0.11

I've implemented a Maintenance Dashboard, so the client on this specific solution, can clean things up.

But perhaps some performance tuning is needed, to handle larger amounts of data, at least so it does not break.

Also, maybe being able to set some clean up rules for carts, as you can for node versions, could be a great add-on.

[mattbrailsford]

I like the idea of some cart clean up routines so that's defo something we could look at.

We've got our initial 6 month roadmap set out, but I think performance could for sure feature on the next iteration.

In this solution do you have any discounts or anything setup? Also, was this an upgraded solution? or has it always been a v3 solution? Another thing to try could also be rebuilding the DB indexes to see if that helps too. Maybe we could introduce that as a health check or something.

[MichaelNielsenDK]

Discounts looks like this
https://prnt.sc/tEQt5FoO3rqF

And yes, I believe the project started on Vendr v1, and has continuously been upgraded to it's current version. It has also been migrated from Umbraco 8 to 10.

It's Umbraco Cloud (Azure) so the Rebuild option is not available in SSMS on the indexes (if anyone else is reading this and also try and look for them).

So it has to be done through a script, though not sure if I should do everything, or only a subset of index and tables.

I'll try on everything on a local backup, and see how that goes.

[mattbrailsford]

Cool, thanks for the additional feedback.

I am aware discounts can affect the load time a little as when repopulating orders it checks the discounts again to see if any apply so those can add up too, but yea, I think it sound mostly like a loading from the DB thing.

The reason I ask about upgrading is because in v1 we did use plain old guids for ID's but I think it was in v2 we introduced combs for ID's instead. The reason to use combs is because they index better in SQL and so cause less fragmentation. But this will only really help if there are no legacy guid ID's because those will continue to be random and cause index fragmentation.

It will be interesting to see though if rebuilding your index does improve stuff too.

[MichaelNielsenDK]

It seems to be a lot faster on a local copy of the DB. Not sure if it's the difference in machine/server, or something happens during export/import.

Rebuilding the DB indexes locally did not give any further perceived performance boost. I haven't tried on Live yet.

Runnig it locally dd give more some more detailed debugging information, and it seems that the reason for breaking, is this error message in the cart list
https://prnt.sc/bToYx3U4dA96

It comes when I go to the last page of the cart list.

I've tried to compare the earliest carts with the latest, but can't find anything seemingly different about them.

I've also tried to delete them using the OrderService, but I get the same error.

I can view and delete carts from a later date, so i think it's a problem with carts prior to a specific date (don't know what that date is yet). Could it be something updating Vendr og Migrating from Umbraco 8 to 10?

Full Stack Trace is

at Vendr.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
   at Vendr.Core.VendrUnitOfWorkProvider.Execute[T](Boolean autoComplete, Func`2 action)
   at Vendr.Core.Services.OrderService.SearchOrders(IQuerySpecification`1 query, ISortSpecification`1 sort, Int64 currentPage, Int64 itemsPerPage)
   at Vendr.Core.Services.OrderService.SearchOrders(IQuerySpecification`1 query, Int64 currentPage, Int64 itemsPerPage)
   at Vendr.Core.Services.OrderService.SearchOrders(Func`2 query, Int64 currentPage, Int64 itemsPerPage)
   at Vendr.Umbraco.Web.Controllers.VendrCartController.SearchCarts(CartSearchDto model)
   at lambda_method4769(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

[mattbrailsford]

Hmm, I'm trying to think why the search method would trigger a store lookup as it should be being passed the store id 🤔

The error usually occurs though when the related product node is not in a tree with an ancesstor with a store picker on it, or if the lucene index is corrupt. I wonder though if one of the products has been deleted and is either in the trash, or doesn't exist. Still, I'm not 100% what is doing the store lookup here. It could be one of the query filters 🤔

[MichaelNielsenDK]

As mentioned, it does the same in my cleanup task, where this is the query

_orderService.SearchOrders(x => x.FromStore(storeId) .And(x.CreatedAfter(fromDate)) .And(x.CreatedBefore(toDate.AddDays(1))) .AndNot(x.IsFinalized()) , itemsPerPage: 100);

I have tried rebuilding everything, caches and indexes.

I would assume it would be fine to have either deleted products, or products in the recycle bin.

You wouldn't have an SQL script for deleting an order on hand, there many references to many tables. If not, I'll put it together myself, and see if I can clear some stuff out, and see if I can figure out what is going on.

[mattbrailsford]

Hey Michael,

This is the only script I have which is for deleting everything, but it at least outlines the tables you'll need to be working with

DELETE FROM vendrFrozenPrice
DELETE FROM vendrOrderLineAttribute
DELETE FROM vendrOrderLineProperty
DELETE FROM vendrOrderLine
DELETE FROM vendrOrderPriceAdjustment
DELETE FROM vendrOrderAmountAdjustment
DELETE FROM vendrOrderAppliedGiftCard
DELETE FROM vendrOrderAppliedDiscountCode
DELETE FROM vendrOrderProperty
DELETE FROM vendrOrder

And again, thanks for the feedback.

RE deleted products etc, I agree, and like I mentioned last time I'm not sure what is triggering looking up the store as I don't think that should be necessary so I will look into that, my suggestion was only really to see if it could improve anything for you right now 👍

[MichaelNielsenDK]

Thanks, I appreciate the help.

As it turns out, the SQL did not seem as complicated as I first thought. I just looked at all the foreign keys, but naturally it's only those related to OrderId or OrderLineId that is relevant.

So I ended up with this

BEGIN TRANSACTION;

-- Select all carts before date
SELECT * INTO #tempOrders FROM vendrOrder WHERE createDate < '2023/06/16' AND (finalizedDate IS NULL) 

-- Delete data referenced to orders above in related tables
DELETE FROM vendrFrozenPrice WHERE vendrFrozenPrice.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderPriceAdjustment WHERE vendrOrderPriceAdjustment.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderAmountAdjustment WHERE vendrOrderAmountAdjustment.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderAppliedDiscountCode WHERE vendrOrderAppliedDiscountCode.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderAppliedGiftCard WHERE vendrOrderAppliedGiftCard.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderLineAttribute WHERE vendrOrderLineAttribute.OrderLineId IN (SELECT Id FROM vendrOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));
DELETE FROM vendrOrderLineProperty WHERE vendrOrderLineProperty.OrderLineId IN (SELECT Id FROM vendrOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));
DELETE FROM vendrOrderProperty WHERE vendrOrderProperty.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrderLine WHERE vendrOrderLine.OrderId IN (SELECT Id FROM #tempOrders) AND vendrOrderLine.parentOrderLineId IS NOT NULL;
DELETE FROM vendrOrderLine WHERE vendrOrderLine.OrderId IN (SELECT Id FROM #tempOrders);
DELETE FROM vendrOrder WHERE vendrOrder.Id IN (SELECT Id FROM #tempOrders);

-- Drop the temporary table
DROP TABLE #tempOrders;

-- Use ROLLBACK for testing
-- ROLLBACK;

-- Use COMMIT for deleting
COMMIT;

This cleared out all carts older than a week, and now the error I had is gone, and everything is fast again. 👍🥳

I also added a simple Maintenance Dashboard, so moving forward, the client can select a date and delete alle carts or orders created prior to that date.

[mattbrailsford]

Awesome work @MichaelNielsenDK and really appreciate you sharing your SQL script. I'm sure this will a) come in useful for others and b) be super useful for myself when I get to address this 👍