A curious case of SQL execution plan

I said this already, and I will say it again: SQL Server optimizer is smart. I can even go further and say, it’s smarter than you and me (I have no doubt that you are smart, even very, very smart 🙂 ). So most of the cases, you leave it to do whatever it thinks is the best.

But there are cases SQL Server optimizer is fooled by the engine – it gets confused and chooses an sub-optimal plan, because it was given wrong, outdated, or incorrect information. That’s when you need to step in.

Today I face one case like that, as reported here: http://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/10/database-timeout-on-productvariant-update/

Continue reading “A curious case of SQL execution plan” →

Episerver Commerce catalog performance optimization – part 4

Recently I worked on a support case where a customer reported deadlocks and timeout exceptions on queries to a specific table – NodeEntryRelation. Yes, it was mentioned in this post. However, there is more to it.

Keeping the indexes healthy definitely help to improve performance and avoid deadlocks and timeout exceptions. However it can only work to a limit, because even if the indexes are in their perfect state (the fragmentation level is 0%), the query will still take time.

Looking in the query we talked about – ecf_Catalog_GetChildrenEntries – what does it do. It lists the entries which are direct children of a catalog. So normally entries belong to categories (nodes), but it’s possible (Although not recommended) to have entries that belong directly to a catalog.

Continue reading “Episerver Commerce catalog performance optimization – part 4” →

Reindex obsolete prices in Episerver Commerce

Recently I stumped upon this question:

http://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/8/event-for-price-becoming-validinvalid/

which is very interesting to me. I can see this is a real scenario – and even quite common. When a price become obsolete, you want your contents to be reindexed so the next time you query, the search result will be returned correctly. But how?

Continue reading “Reindex obsolete prices in Episerver Commerce” →

Maintaining your indexes

Indexes are crucial to SQL Server performance. Having the right indexes might make the difference of day and night with your application performance – as I once talked here.

However, even having the right indexes is not everything. You have to keep them healthy. Indexes, as any other kinds of storage, is subjected to fragmentation. SQL Server works best if the index structure is compact and continuous, but with all of the inserts/updates/deletes, it’s inevitable to get fragmented. When the fragmentation grows, it starts affecting the performance of SQL Server: Instead of having to read just one page, it now have to read two, which increases both time and resource needed, and so on and so forth.

Continue reading “Maintaining your indexes” →

Permanently drop prices of all products

This is an unusual post – as I usually don’t post sample code – that should be the job of the documentation. However, I jumped upon this question http://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/8/global-price-increase/, and found it to be an interesting case to demo.

It’s worth noting that as a customer, I’d like price drops, not the way around, so in this example, we will see how to cut prices of all products to 5%, instead of making them 5% more. Of course, it’s just simple mathematics, so you can change to the formula however you want.

Continue reading “Permanently drop prices of all products” →

Watch out for orphan rows in Episerver Commerce database

One of the most important aspect of data storage is integrity, meaning that the accuracy and consistency is maintained throughout the life cycle. However, there are cases when the integrity can be compromised in certain tables. It is difficult (if not impossible) to enforce a constraint between those columns technically, because of the schema design. The data integrity is enforced by other means (such as triggers, or carefully written stored procedures).

If you are using Episerver APIs, it’s guaranteed that no orphan rows are left behind. However, if you are doing data manipulation directly yourself (which we generally advise against), or if you are syncing databases between environments (for example, between production and development, or between development and UAT), there might be chances that the there are rows that meant to be deleted, but were left behind unintentionally.

Continue reading “Watch out for orphan rows in Episerver Commerce database” →

Episerver Commerce commandTimeout configuration

We at Episerver take performance seriously – as one of the feature that constantly monitored and fine-tuned. This is especially true for database accesses, as they are usually the bottlenecks of the system (accessing databases are I/O operations and in most of the cases it’s much more expensive than reading/writing to memory, or even some complex computation in promotions)

However, we can’t always make our queries blazing fast. In cases when the data set is simply too big, it will take time for SQL Server to complete it, no matter how smart the query was written, or how efficient the indexes were added. In some extreme cases when the data set is big enough, it will result in the infamous exception System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Of course, in such cases, the best solution is to take another approach. Is it possible to restructure your data (for example, catalog), to make it smaller chunks that SQL Server can swallow? Or instead of loading all at once, you can try to load by small batches?

Continue reading “Episerver Commerce commandTimeout configuration” →

Episerver caching issue with .NET 4.7

Update 1: The bug is fixed in .NET 4.7.1 (thanks to Pascal van der Horst for the information)

Update 2: The related bug is fixed in CMS Core 10.10.2 and 9.12.5. If upgrading to that version is not an option, you can contact Episerver support service for further assistance.

Original post:

If you are using Episerver and update to .NET 4.7 (even involuntarily, such as you are using DXC/Azure to host your websites. Microsoft updated Azure to .NET 4.7 on June 26th) , you might notice some weird performance issues. If your servers are in Europe, Asia or Australia, then you can see a peak in memory usage. If your servers in North America, then you can see the number of database calls increased. In both cases, your website performance is affected, the former can cause your websites to constantly restarts as memory usage reaches a threshold limit, and even more obvious in the latter. Why?

It was a known issue in .NET 4.7, as mentioned here: https://support.microsoft.com/en-us/help/4035412/fix-expiration-time-issue-when-you-insert-items-by-using-the-cache-ins

Continue reading “Episerver caching issue with .NET 4.7” →

Price optimizing: to be or not to be

It can be quite confusing when you first edit prices in Episerver Commerce. To your surprises, some of the prices you imported or edited might disappear, or change, without your consent! What happened?

To answer that question, it’s essential to know there are two pricing system in Commerce: IPriceService and IPriceDetailService.

They have some different characteristics, and one of them is very important: the default implementation of IPriceDetailService saves prices as-is, while the default implementation of IPriceService does not: it optimizes prices before saving. Prices which are best for customers will be favored over the “less good” ones.

Continue reading “Price optimizing: to be or not to be” →

Episerver CMS performance optimization – part 1

Update: In Episerver CMS 11, released today (November 21st 2017), the simpleaddress router has been moved to the last of the route table.

Original post:

This is an unusual post – it is not about Commerce – my area of expertise, but about CMS. Recently I’ve been working on some support cases where SQL Server instance is on high utilization, and in some scenarios it eventually slows down the site. After investigation, it’s likely to come from a small, simple and helpful feature: Simple address.

CMS content can have a property named “Simple address”, which allows you to create a “shortcut” url for that content. So if you have one page with “name in url” as “contact-us” under a page name “about-us” under Home page, you can access it via https://mysupercoolsite.com/en/about-us/contact-us. Or you can set the Simple address for that page as “contact-us”, and then you can access it directly via https://mysupercoolsite.com/contact-us.

Continue reading “Episerver CMS performance optimization – part 1” →