Commerce

Index or no index, that’s the question

If you do (and you should) care about your Episerver Commerce site performance, you probably know that database access is usually the bottleneck. Allowing SQL Server works smoothly and effectively is a very important key to the great performance. We are of course, very well aware of this fact, and we have spent a considerable amount of time making sure Commerce database works as fast as we could. Better table schema, better stored procedures, better indexes, ... we have done all of that and will continue doing so when we have the chances. (And if you find anything that can be improved, you are very welcome to share your finding with us) But there are places where the database performance improvement is in your hand. (more…)

By vimvq1987, ago
Catalog

Multiple sites: Building the outgoing URLs

In previous recipe we talked about multiple catalogs with same "UriSegment" - which we had a working implementation for incoming URL, i.e. when a customer visit a product url, we know which catalog we should choose from. But we still need to cover the generation of outgoing URL. I.e. when we link a product (For example, from a campaign page), we need to generate an URL which take the "catalog-less" pattern into account. We need to understand how the outgoing URL is built. The hierarchical router builds the URL by the RouteSegment of contents. However, we want to the urls appear to have same catalog, so the RouteSegment part for the catalogs must be the same, regardless of the true catalogs. Because all catalogs are on same level, their RouteSegment must be unique - and this is enforced from Framework level (which is understandable, otherwise, how can it know which content to choose). (more…)

By vimvq1987, ago
Catalog

Multiple catalogs with same url

This is an excerpt from my second book .¬†The first chapter is available to read for free. A business is having an Episerver Commerce instance with multiple sites and multiple catalogs set up. They want to make sure each site will use one catalog, and all of them will share the same url for catalog structure. So it'll be "https://site-a.com/products/category/", and "https://site-b.com/products/category/". Site A and site B are using different catalogs. Is this doable? Yes! It's just a matter of magic with the routing. This time, we would need to do an implementation of HierarchicalCatalogPartialRouter¬†ourselves. First, let's create a template for it: [crayon-5ad608c56d94e512844959/] (more…)

By vimvq1987, ago
Catalog

Speed up your Catalog incremental indexing

As your products are being constantly updated, you would naturally want them to be properly (and timely) indexed - as that's crucial to have the search results that would influence your customers into buying stuffs. For example, if you just drop the prices of your products , you would want those products to appear in new price segment as soon as possible. This should be very easy with Find.Commerce - so if you are using Find (which you should) - stop reading, nothing for you here. Things, however, can be more complicated if you are using the more "traditional" SearchProvider. (more…)

By vimvq1987, ago
Commerce

Why you should upgrade to the latest version

I made no secret that I'm a die-hard advocate for upgrading to latest EPiServer CMS/Commerce version. There are several reasons for that, mostly from new shiny features that your businesses dearly need, new big performance improvements that your customers firmly demand. But there is another, not so obvious reason: support. Let me tell you a story. This morning we received a support case from support team. A customer recently upgraded from Commerce 7.5 (Eww) to 11.7 (Yay!), things went well except they had a small problem with data displaying in Catalog UI. Some of the properties were not properly displayed, but they are still showing correct in Commerce Manager. (more…)

By vimvq1987, ago
Catalog

Mass update catalog entries

This is something you don't do daily, but you will probably need one day, so it might come in handy. Recently we got a question on how to update the code of all entries in the catalog. This is interesting, because even thought you don't update the codes that often (if at all, as the code is the identity to identify the entries with external system, such as ERPs or PIMs), it raises a question on how to do mass update on catalog entries.

    • Update the code directly via database query. It is supposedly the fastest to do such thing. If you have been following my posts closely, you must be familiar with my note regarding how Episerver does not disclose the database schema. I list it here because it's an option, but not the good one. It easily goes wrong (and cause catastrophes), you have to deal with versions and cache, and those can be hairy to get right. Direct data manipulation should be only used as the last resort when no other option is available.
(more…)

By vimvq1987, ago
Catalog

A curious case of SQL Server function

This time, we will talk about ecfVersion_ListFiltered, again. This stored procedure was previously the subject of several blog posts regarding SQL Server performance optimizations. When I thought it is perfect (in term of performance), I learned something more. Recently we received a performance report from a customer asking about an issue after upgrading from Commerce 10.4.2 to Commerce 10.8 (the last version before Commerce 11). The job "Publish Delayed Content Versions" starts to throw timeout exceptions. This scheduled job calls to a ecfVersion_ListFiltered to load the content versions which are in status DelayedPublish, it looks like this when it reaches SQL Server: [crayon-5ad608c56e63e210191282/]This query is known to be slow. The reason is quite obvious - Status contains only 5 or 6 distinct values, so it's not indexed. SQL Server will have to do a Clustered Index Scan, and if ecfVersion is big enough, it's inevitably slow. (more…)

By vimvq1987, ago
Commerce

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. (more…)

By vimvq1987, ago
Commerce

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. (more…)

By vimvq1987, ago
Catalog

Please, rebuild your database indexes, now

I will make it quick and to the point: if you are expecting a lot of customers visiting your site tomorrow (and you should) for Black Friday, you should rebuild your database indexes, now. On average, it will help you to serve more customers and they will be happier with a more responsive, faster website. On best cases it will help prevent catastrophes. (more…)

By vimvq1987, ago