C#, SQL, Episerver/Optimizely, performance, and beyond
Recently I stumped upon this question:
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”
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.
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.
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”
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”
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
This is more of a note-to-self.
I sometimes have to import a bacpac file from customer’s database (usually from Azure SQL database) to my local machine – . For most of the time it’ll be very easy when the databases are in .bak format, but for .bacpac file it can be pretty complicated.
Sqlpackage.exe is the standard tool to import the .bacpac file, and it can be found with the installation of Visual Studio (for example `C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130`) or SQL Server ( `C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin` ). Latest version should be used because they can support the older formats (.bacpac exported from older SQL Server version), but not the way around (older version might not support .bacpac files exported from newer SQL Server versions)
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.
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”
It’s quite common when a customer browses your site without logging in – either she/he intentionally does that, or just forget about logging in. The customer might add some items to carts and even checks out, but then is asked to log in or remember to log in. What would happen?
By default, Episerver Commerce will do as following: