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”
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”
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 trigger, or careful 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 takes 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.11. If upgrading to that version is not an option, you can contact Episerver support service for further assistance.
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”
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)
Continue reading “Import a bacpac to SQL Server”
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:
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”
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:
- Attach all orders made by that section to logged in customer. (I once placed an order in a famous retailer in Sweden without logging in, and then I asked their customer service to link that order to my account so I can track it easier. To my surprise, it cannot be done! That’s why I personally appreciate this feature.)
- Merge all carts to existing carts, by name and market. So if I’m currently in US market and I added a item to that cart, and the US-cart linked to my account already have 2 other items, then when I log in, my US-cart will contain 3 items.
- Merge all wishlist to existing wishlists, also per market. (The name for wishlist, by default, is fixed to “Wishlist”)
Continue reading “Merging carts when a customer logs in”
It seems that we currently have an increasing number of questions regarding multisite feature in Episerver Commerce. It’s fully supported in CMS. You can have only one site, one database to serve multiple domains – which of course reduces the cost. But can it be done in Commerce as well?
The answer is (as almost always): it depends on your definition of “multisite”: multisite can be that customers see multiple domains, which probably different styles, but in the backend everything is the same – you share the same collection of orders and customers. Or multisite can be that there is a separation in backend: each site has its own collection of orders and customers.
Continue reading “Multisite in Episerver Commerce”