Don’t let order search kill your site

Episerver Commerce order search is a powerful feature. My colleague Shannon Gray wrote about is long ago https://world.episerver.com/blogs/Shannon-Gray/Dates/2012/12/EPiServer-Commerce-Order-Search-Made-Easy/ , and I myself as well https://world.episerver.com/blogs/Quan-Mai/Dates/2014/10/Order-searchmade-easy/

But because of its power and flexibility, it can be complicated to get right. People usually stop at making the query works. Performance is usually an after thought, as it is only visible on production environment when there are enough requests to bring your database to its knees.

Let me be very clear about it: during my years helping customers with performance issues (and you can guess, that is a lot of customers), order search is one of the most, if not the most common cause of database spikes.

Trust me, you never want to your database looks like this

As your commerce database is brought to its knees, your entire website performance suffers. Your response time suffers. Your visitors are unhappy and that makes your business suffer.

But what is so bad about order search?

Order search allows you to find orders by almost any criteria. And to do that, you often join with different tables in the database. Search for orders with specific line items? Join with LineItem table on a match of CatalogEntryId column. Search for orders with a specific shipping method? Join with Shipment table on a match of ShippingMethodId etc. etc. SqlWhereClause and SqlMetaWhereClause of OrderSearchParameters are extremely flexible, and that is both a cure, and a curse.

Let’s examine the first example in closer details. The query is easy to write. But don’t you know that there is no index on the CatalogEntryId column? That means every request to search order, end up in a full table scan of LineItem.

There are two bad news into that: your LineItem table usually have many rows already, which makes that scan slow, and resource intensive. And as it’s an ever growing table, the situation only gets worse over time.

That is only a start, and a simple one, because that can be resolved by adding an index on CatalogEntryId , but there are more complicated cases when adding an index simply can’t solve the problem – because there is no good one. For example if you search for orders with custom fields, but only of type bit . Bit is essentially the worst type when it comes to index-ability, so your indexes will be much less effective than you want it to be. A full table scan will likely be used.

In short:

Order search is flexible, and powerful. But, “With great power come great responsibility”. Think about what you join on your SqlWhereClause and SqlMetaWhereClause statements, and if your query is covered by an index, or if adding an index will make senses in this case (I have a few guidelines here for a good index https://vimvq1987.com/index-or-no-index-thats-the-question/). Or if you can limit the number of the orders you search for.

Your database will thank you, later.

Iterate through all carts/orders

While it’s not a common task to do, you might want to iterate through all carts, or all carts with a specific criteria. For example, you might want to load all carts that have been last modified for more than 1 week, but less than 2 weeks, so you can send a reminder to the buyer (Ideas on the implementation of that feature is discussed in my book – Episerver Commerce A problem solution approach). Or you simply want do delete all the carts, as asked here https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2021/1/removing-all-active-carts/ . How?

In previous versions of Episerver Commerce, what you can do is to use OrderContext to find orders and carts using the Order search API. However that does not work with non default implementations, such as the serializable carts. A better way would be to use the new abstraction – IOrderSearchService. It takes a OrderSearchFilter which allows things like paging to be set, and returns an OrderSearchResults<T> which contains the matching collection of carts or orders, and the total count. When you have a lot of carts or orders to process, it’s nice (even important) to let the end users know the progress. However, it’s also important to know that, counting the matching carts/orders can be very expensive, so I’d suggest to avoid doing it every time.

The pattern that you can use is to do a first round (which do not load many carts, except one), to load total count. For subsequent calls you only load the carts, but set ReturnTotalCount to false to skip loading the total count. If you want to delete all the carts (for fun and profit, obviously do not try this on production, unless if this is exactly what you want), the code can be written like this, with _orderSearchService is an instance of IOrderSearchService, and _orderRepository is an instance of IOrderRepository

            var deletedCartsTotalCount = 0;
            var cartFilter = new CartFilter
            {
                RecordsToRetrieve = 1,
                ExcludedCartNames = excludedCartNames,
                ReturnTotalCount = true
            };

            //Get the total carts for status update.
            var orderSearchResults = _orderSearchService.FindCarts(cartFilter);
            var totalCount = orderSearchResults.TotalRecords;
            cartFilter.ReturnTotalCount = false;
            cartFilter.RecordsToRetrieve = 100;

            var cartLoaded = 0;
            do
            {
                var searchResults = _orderSearchService.FindCarts(cartFilter);
                foreach (var cart in searchResults.Orders)
                {
                    _orderRespository.Delete(cart.OrderLink);
                    deletedCartsTotalCount++;
                }

                OnStatusChanged($"Deleted {deletedCartsTotalCount} in {totalCount} carts.");
                cartLoaded = searchResults.Orders.Count();
            }
            while (cartLoaded > 0);

A few notes:

You might or might not exclude carts based on name
CartFilter has a few filters that you can play with, not just names.

Control the thousand separator for Money in Episerver Commerce

If you are selling goods in multiple markets which same currency but with different languages, such as EuroZone, you might notice that while everything looks quite good, except that the thousand separator might be off from time to time: it is always the same and does not change to match with the language, so sometimes it’s correct, sometimes it’s not.

Let’s take a step back to see how to properly show the thousand delimiter

In the United States, this character is a comma (,). In Germany, it is a period (.). Thus one thousand and twenty-five is displayed as 1,025 in the United States and 1.025 in Germany. In Sweden, the thousands separator is a space.
https://docs.microsoft.com/en-us/globalization/locale/number-formatting

You might ask why the problem happens with Episerver Commerce. In Commerce, each currency has an attached NumberFormatInfo which let the framework knows how to format the currency. During startup, the system will loop through the available CultureInfo and assign its .NumberFormat to the currency.

The problem is there might be multiple CultureInfo that can handle same currency, for example, EUR which is used across Eurozone, can be handled by multiple (20? ) cultures. However, the first matching CultureInfo to handle the format of the currency will be used. In most of the cases, it will be br-FR (because the CultureInfo(s) are sorted by name, and this CultureInfo is the first in the list to handle EUR)

br-FR does not have a thousand separator, but a whitespace. That’s why even if your language is de-DE, the amount in EUR will not be properly formatted as 1.234,45 but 1 234,45

How to fix that problem?

Luckily, we can set the NumberFormatInfo attached for each currency. If you are only selling in Germany, you can make sure that EUR is always formatted in German style, by adding this to one of your initialization modules:

var culture = CultureInfo.GetCultureInfo("de-DE");
Currency.SetFormat("EUR", culture.NumberFormat);

But if you have multiple languages for one currency, this will simply not work (because it’s static, so it will affect all customer). Your only option is to avoid using Money.ToString(), but to use Money.ToString(IFormatProvider), for example

money.ToString(CultureInfo.CurrentUICulture);

Assuming CultureInfo.CurrentUiCulture is set to correct one.

This, however, does not resolve the problem with merchandisers using Commerce Manager. They might have to work with orders from multiple markets, and for example, if your site is selling good stuffs in Europe, there are chances that merchandisers see the prices without correct thousand separator. Most of places in Commerce Manager uses Money.ToString(), and there is a reason for that: it’s too risky to use Money.ToString(CultureInfo.CurrentUICulture), because if a merchandiser uses English, he or she is likely gonna see money formatted as “$” instead of “€”, and that is a much bigger problem of itself.

Moral of the story: localization is hard, and sometimes a compromise is needed.

Commerce batching performance – part 2: Loading prices and inventories

UPDATE: When looked into it, I realize that I have a lazy loading collection of entry codes, so each test had to spent time to resolve the entry code(s) from the content links. That actually costs quite a lot of time, and therefore causing the performance tests to return incorrect results. That was corrected and the results are now updated.

In previous post we talked about how loading orders in batch can actually improve your website performance, and we came to a conclusion that 1000-3000 orders per batch probably yields the best performance result.

But orders are not the only thing you would need to load on your website. A more common scenario is to load prices and inventories for product. So If you are displaying a product listing page, it’s quite common to load prices and inventories for all products in that page. How should it be loaded?

Continue reading “Commerce batching performance – part 2: Loading prices and inventories” →

Commerce batching performance – part 1: Loading orders

One of best practices for better performance – not just with Commerce or Episerver Commerce, is to batch your calls to load data. In theory, if you want to load a lot of data, loading by both end will be problematic: if you load each record one by one, the overhead for opening the connection and retrieve data will be too much. But if you load all of them, then it is likely that you will end up with either time out exception in database end, or out of memory exception in your application. The better way is to of course, loading them by smaller batch: either 10, 20, or 50 records at one and repeat until the end.

That is the theory, but is it really better in practice? And if it is, which size of batch works best? As they usually say, reality is the golden test for theory, so let’s do it.

Continue reading “Commerce batching performance – part 1: Loading orders” →

Package equivalent promotion type in Episerver Commerce

Recently we got this question on how to create package-equivalent promotion type in Episerver Commerce, from https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2018/2/is-there-a-built-in-group-discount/

I already recommended to use package for such purpose, because of several reasons:

Package is a builtin feature, and is fully supported by the framework, both on UI level and API level.
It has been well tested and is very reliable to use.

However in a real world implementation, it might not be easy to just add package implementation. One reason would be if you rely on an external PIM to handle your catalog. Configuring it to support package can not be trivial.

So why not try to implement a package equivalent promotion in the promotion engine, to see if it works.

Continue reading “Package equivalent promotion type in Episerver Commerce” →

Optimizing T-SQL COUNT

This is a continuation of my previous post about paging in SQL Server. When it comes to paging, you would naturally want to know the total number of rows satisfying, so you can display some nice, useful information to your end-users.

You would think, well, it’s just a count, and a simple query like this would be enough:

SELECT COUNT(Id) FROM MySecretTable

There should be nothing to worry about, right? Actually, there is.

Let’s get back to the example in previous post – we have to count the total number of orders in that big table.

SELECT COUNT(ObjectId) FROM OrderGroup_PurchaseOrder

Because ObjectId is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index and be pretty fast. But does it? To my surprises, no.

Continue reading “Optimizing T-SQL COUNT” →

The art of paging

No this is not really “art” – I’m just trying to have a more clickbait title. It’s more about understanding what you have at your disposal and use them for your benefits – in this case – how new SQL statement can drastically improve your performance.

In this blogpost we will look into paging feature of SQL Server. in Commerce we usually work with large set of data – millions of rows are fairly common, and it’s natural to load data by page. There is no point loading thousands, or even millions of rows in one go. First it’s not practical to display all of them. Second you’ll likely end up with an timeout exception and/or an out of memory exception. Even if you are lucky enough to get through, it’s still able to take your SQL Server instance to a knee, and transferring that much data over network will be another bottleneck for your system. So my friends, the best practice for loading data is to do it by batches, and to not load everything at once.

Continue reading “The art of paging” →

Fixing a stored procedure

At Episerver development team, we understand the importance of good performance. Who would not like a lightning fast website? We work hard to ensure the framework is fast, and we seize (almost) every opportunity to make it faster.

You know in Commerce 10.2 we introduced a new cart mode – serializable cart, and it’s proven to bring great performance compared to the “old/traditional” approach. Our own tests showed an improvement of 3-5x times faster. But can it be even faster? Probably yes.

And actually we did some improvements in later versions. In the scope of this blog post, we will just focus into a specific aspect – and to learn a little more about SQL Server performance optimization.

Continue reading “Fixing a stored procedure” →