How to store a basket against a user in Ucommerce efficiently

TLDR; If you want to tie a basket to the logged in user, don't follow the Ucommerce documentation if you're using a platform with a Guid as the member ID as it's a performance nightmare, instead use our alternative approach.

The Issue

We recently had an issue where a client informed us that their E-commerce website was timing out during the checkout flow, causing issues where payment was taken and the basket was not updated as expected. This particular website has the requirement where a UCommerce basket should only be tied to a logged in user, as you must be logged in to add a product to the cart, and thus can’t use the default cookie-based mechanism that ties a basket to a particular browser.

We followed the initial instructions from UCommerce documentation (https://docs.ucommerce.net/ucommerce/v9.7/extending-ucommerce/change-default-basket-behavior.html ), which is to inherit from the OrderContext class and implement overrides to GetBasket().

The main change to this code is to check if the user is logged in first and if they are, instead of just creating a basket and storing it in a cookie, will instead create an order property on the basket PurchaseOrder with the members id and store that, then when retrieving the basket, will search for all PurchaseOrders that have the order property of ‘MemberId’ and check the value is the same as the current logged in user and then check they are in Basket state, rather than just checking the cookie in the browser.

If the user is not logged in, a default basket will still be created that is lost when they do log in as per the original implementation in OrderContext.

The issue that we have faced, which is not highlighted is that this approach was most likely created under the impression that a memberId is an integer value, which for the CMS system this website uses is not the case as it is a guid. It also checks for the OrderStatus of Basket last, which means it does more work up front, before limiting down the result to just Basket Orders.

That means that this statement is not performant as it is doing a lookup on a string Guid in a table that is joined to the Purchase Order table for all orders, then filtering by basket. This was the root cause of the issue we were seeing. It could also just do .Any() instead of .Count() > 0

PurchaseOrder order = PurchaseOrder.SingleOrDefault(x => x.OrderProperties.Where(y => y.Order.OrderId == x.OrderId
&& y.Key == "MemberId"
&& y.Value == MemberService.GetCurrentMember().MemberId.ToString()
).Count() > 0
&& x.OrderStatus.OrderStatusId == 1); // 1 == BasketId

It is also worth mentioning that it uses SingleOrDefault, which is not performant as it can just be FirstOrDefault. SingleOrDefault will loop the entire collection to ensure there is only 1 and throw an expection, whereas FirstOrDefault will leave the iterator as soon as it finds a match.​

An Alternative Approach

To still have the functionality we need, but without this cumbersome LINQ/SQL statement, it seemed appropriate to instead take the MemberId, which as we know is a guid, and just assign that as the BasketId on the purchase order when it is created. This works as the BasketId is a transient value, which is converted to NULL on a purchase order once the checkout pipeline has run. When a user finishes their order and completes check-out and then come back to the site, they will get a new basket with their memberId and the process starts all over again.

The above statement therefore becomes this when searching for a basket

var order = PurchaseOrder.FirstOrDefault(x => x.OrderStatus.OrderStatusId == (int)PurchaseOrder.StatusCode.Basket && x.BasketId == MemberService.GetCurrentMember().MemberId);

and then when setting a basket for the user the code has been changed from:

PurchaseOrder order = new PurchaseOrder();
order.OrderStatus = OrderStatus.Get((int)PurchaseOrder.StatusCode.Basket);
order.ProductCatalogGroup = store;
order.BillingCurrency = currency;
order.BasketId = Guid.NewGuid();
order.CreatedDate = DateTime.Now;
order.Save();
  
// Set the member id on the order so we can retrieve it later on
order["MemberId"] = MemberService.GetCurrentMember().MemberId.ToString();

to:

PurchaseOrder order = new PurchaseOrder
{
    OrderStatus = OrderStatus.Get((int)PurchaseOrder.StatusCode.Basket),
    ProductCatalogGroup = store,
    BillingCurrency = currency,
    BasketId = MemberService.GetCurrentMember().MemberId,
    CreatedDate = DateTime.UtcNow
};
order.Save();

Under the hood UCommerce translates the LINQ statement executed when searching for a user’s basket to the following SQL statements:

Original Query (Using an Order Property):

DECLARE @p0 NVARCHAR(4000)
DECLARE @p1 NVARCHAR(max)
DECLARE @p2 INT
DECLARE @p3 INT

SET @p0 = 'MemberId'
SET @p1 = '072a1f46-0c28-6c02-8629-ff04005ae238'
SET @p2 = 0
SET @p3 = 1
        

SELECT TOP (1) purchaseor0_.OrderId AS orderid1_83_
    ,purchaseor0_.BasketId AS basketid2_83_
    ,purchaseor0_.OrderNumber AS ordernumber3_83_
    ,purchaseor0_.CultureCode AS culturecode4_83_
    ,purchaseor0_.CreatedDate AS createddate5_83_
    ,purchaseor0_.ModifiedOn AS modifiedon6_83_
    ,purchaseor0_.CompletedDate AS completeddate7_83_
    ,purchaseor0_.Note AS note8_83_
    ,purchaseor0_.VAT AS vat9_83_
    ,purchaseor0_.OrderTotal AS ordertotal10_83_
    ,purchaseor0_.Discount AS discount11_83_
    ,purchaseor0_.DiscountTotal AS discounttotal12_83_
    ,purchaseor0_.ShippingTotal AS shippingtotal13_83_
    ,purchaseor0_.PaymentTotal AS paymenttotal14_83_
    ,purchaseor0_.TaxTotal AS taxtotal15_83_
    ,purchaseor0_.SubTotal AS subtotal16_83_
    ,purchaseor0_.OrderGuid AS orderguid17_83_
    ,purchaseor0_.Guid AS guid18_83_
    ,purchaseor0_.CustomerId AS customerid19_83_
    ,purchaseor0_.OrderStatusId AS orderstatusid20_83_
    ,purchaseor0_.CurrencyId AS currencyid21_83_
    ,purchaseor0_.ProductCatalogGroupId AS productcataloggroupid22_83_
    ,purchaseor0_.BillingAddressId AS billingaddressid23_83_
FROM Ucommerce_PurchaseOrder purchaseor0_
WHERE (
        SELECT cast(count(*) AS INT)
        FROM Ucommerce_OrderProperty orderprope1_
        WHERE purchaseor0_.OrderId = orderprope1_.OrderId
            AND (orderprope1_.OrderLineId IS NULL)
            AND (
                orderprope1_.OrderId = purchaseor0_.OrderId
                OR (orderprope1_.OrderId IS NULL)
                AND (purchaseor0_.OrderId IS NULL)
                )
            AND orderprope1_.[key] = @p0
            AND orderprope1_.Value = @p1
        ) > @p2
    AND purchaseor0_.OrderStatusId = @p3

Updated Query (Setting the MemberId as the BasketId):

DECLARE @p4 INT
DECLARE @p5 UNIQUEIDENTIFIER

SET @p4 = 1
SET @p5 = '072a1f46-0c28-6c02-8629-ff04005ae238'

SELECT TOP (1) purchaseor0_.OrderId AS orderid1_83_
	,purchaseor0_.BasketId AS basketid2_83_
	,purchaseor0_.OrderNumber AS ordernumber3_83_
	,purchaseor0_.CultureCode AS culturecode4_83_
	,purchaseor0_.CreatedDate AS createddate5_83_
	,purchaseor0_.ModifiedOn AS modifiedon6_83_
	,purchaseor0_.CompletedDate AS completeddate7_83_
	,purchaseor0_.Note AS note8_83_
	,purchaseor0_.VAT AS vat9_83_
	,purchaseor0_.OrderTotal AS ordertotal10_83_
	,purchaseor0_.Discount AS discount11_83_
	,purchaseor0_.DiscountTotal AS discounttotal12_83_
	,purchaseor0_.ShippingTotal AS shippingtotal13_83_
	,purchaseor0_.PaymentTotal AS paymenttotal14_83_
	,purchaseor0_.TaxTotal AS taxtotal15_83_
	,purchaseor0_.SubTotal AS subtotal16_83_
	,purchaseor0_.OrderGuid AS orderguid17_83_
	,purchaseor0_.Guid AS guid18_83_
	,purchaseor0_.CustomerId AS customerid19_83_
	,purchaseor0_.OrderStatusId AS orderstatusid20_83_
	,purchaseor0_.CurrencyId AS currencyid21_83_
	,purchaseor0_.ProductCatalogGroupId AS productcataloggroupid22_83_
	,purchaseor0_.BillingAddressId AS billingaddressid23_83_
FROM Ucommerce_PurchaseOrder purchaseor0_
WHERE purchaseor0_.OrderStatusId = @p4
	AND purchaseor0_.BasketId = @p5

The Benchmark

1. Run to clear any cache data and level the playing field
2. Run the original query with known values (Without Execution plan monitoring)
3. Run the updated query with known values (Without Execution plan monitoring)
4. Run the SQL below to see the result (remember to turn on the execution plan)

DBCC DROPCLEANBUFFERS -- clear data cache
DBCC FREEPROCCACHE -- clear proc plan cache
SELECT TOP 1000
   creation_time
   ,last_execution_time
   ,total_physical_reads
   ,total_logical_reads
   ,total_logical_writes
   , execution_count
   , total_worker_time
   , total_elapsed_time
   , total_elapsed_time / execution_count avg_elapsed_time
   ,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY execution_count DESC;

As you can see this is a drastic reduction in the time taken for each query to run and the logical reads!

Next, repeat, but turn on the execution plan.

Original Query Execution Plan

Stats for Each Step:

New Solution Execution Plan

Stats for Each Step:

Hopefully you may find this useful if your ever in a similar position with either needing to tie baskets to a user, or have already done something similar, and are finding it not as performant as expected.