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”

Import a bacpac to SQL Server

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”

Merging carts when a customer logs in

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”

Can Episerver Commerce support our catalog size?

One of the questions customers usually raise during evaluation of Episerver Commerce is : “Can it support our catalog size? We have (a very big number of ) entries. Will it work?”

The answer is (of course, as always): It depends!

I’ve seen “big” catalogs. Some big enough in number – 1 million entries catalogs are not very uncommon, and some are even (much) bigger. Theoretically, Episerver Commerce can support up to 512 millions 1 billion of entries (!), so you can have pretty much anything in your catalog until you reach a hard technical limit. Just for comparison, Amazon.com, which is arguably the biggest eCommerce site on the world, has about 500 millions SKU(s) in 2015. But the number of entries is not everything. There are several factors which determine your catalog “size”. the number of entries is an important factor, but there are several other factors as well.

Continue reading “Can Episerver Commerce support our catalog size?”

The Catalog UI trade-off: performance or better UI

I supposed this is a well known feature, but I was asked more than once about it, so it’s better to write something here to clarify the confusions.

If you have some very, very big catalogs, you probably have seen this “notification” in Catalog UI

By default, the Catalog UI groups a product and its variations in a parent-children view (they are not exactly parent-children, by the way). However, to do that, it needs to know about all the entries in that specific category. If it’s a small category, it should be no problem, but if it’s big one, then it’s inevitable slow. The lazy loading which the catalog content list only loads the contents when you scroll to them is not helping in this matter. Moreover, the grouping introduces an overhead for the UI, and having too many groups can severely affect the performance. Trust me, you won’t like a sluggish UI.

This improvement was introduced way back – 7.11 if I remember correctly – thanks to my colleague Magnus Rahl. To this day it’s still valuable – the performance was improved – but not that much to remove the threshold completely (And the improvement to the catalog versioning in Commerce 9 should have nothing to do with this).

When you see this notification, and if you’re unhappy with it, you have two (primary) options: Either to sub-categorize your category – i.e. introduce sub categories so each will have a smaller number of entries. Or increase the value of threshold.

Each approach has its own disadvantages. Sub-categorizing might break your SEO, while the second approach will undoubtedly effect the UI performance. Your call!

Now – the tricky part – which number to configure in SimplifiedCatalogListingThreshold setting. Obviously, it must be greater than the biggest number of entries in a category. But how to obtain that number? I’ve seen the confusion to raise that value to 3000, 5000, or even 10000 and it’s still not working. No, you can’t guess, you have to know for sure.

One simple option is to look at Commerce Manager Catalog Management. There is a small text in right corner of the list which shows the number of entries in that category (No, it’s not available in the Catalog UI, but I assume it would be helpful?)

 

The nuke option is to look at the database. Usually we recommend to avoid manipulate the database directly, as it can be dangerous – but here is a little code which only queries data (so practical harmless)

Now you know the biggest number of the entries in a category – just change the threshold in setting. Try it and see if the UI Performance is acceptable to you.

Find.Commerce is not for Commerce Manager

I’ve seen this more than once, and this can be quite tiresome to fix the problem(s) after that. So here the TL;DR: If you are installing Find.Commerce to Commerce Manager, you are doing it wrong.

You’ll probably end up in the error like this

Continue reading “Find.Commerce is not for Commerce Manager”

Episerver Commerce performance optimization – part 2

Or lock or no lock – that’s the question.

This is the second part of the series on how can you improve the performance of Episerver Commerce site – or more precisely, to avoid the deadlocks and 100% CPU usage. This is not Commerce specific actually, and you can apply the knowledge and techniques here for a normal CMS site as well.

It’s a common and well-known best practice to store the slow-to-retrieve data in cache. These days memory is cheap – not free – but cheap. Yet it is still much faster than the fastest PCIe SSD in the market (if your site is running on traditional HDD, it’s not even close). And having objects in cache means you won’t have to open the connection to SQL Server, wait for it to read the data and send back to you – which all cost time. And if the object you need is a complex one, for example a Catalog content, you will also save the time needed to construct the object. Even if it’s fast, it is still not instantaneous, and it will cost you both memory and CPU cycles. All in all – caching is the right way to go. But how to get it right?

One common mistake for to have no lock when you load the data for the first time and insert it into cache.

Continue reading “Episerver Commerce performance optimization – part 2”

The hidden danger of dot (Or why should your metafield not contain . in the name)

A dot (.) – it is harmless. What harm can it do, it looks pretty innocent.

And yet it can break your Catalog UI.

Psyduck, from Pokemon Go
A dot can look pretty harmless and innocent, just like a Psyduck. Frankly, its eyes are also two dots.

Catalog UI relies on the Shell UI from CMS to render properties and such. Shell UI, in its hands, needs to know about the metadata of the properties. When you have dot in the metafield names, the MetaDataPropertyMapper will create an Property with that name on site start up. And then when you open All properties mode, Shell UI will request your content type models, and CMS Core will happily return those properties.

Continue reading “The hidden danger of dot (Or why should your metafield not contain . in the name)”

Fixing Visual Studio 2015 after update 3

In case you did not notice, Microsoft released Visual Studio 2015 Update 3 a couple of days ago. I immediately jumped in because I have high hope for better stability – VS2015 update 2 has been crashing more open than I would like.

When I updated my VS on my work computer, all went well and it worked right after that without any problem. However when I finally updated my VS on my home computer, problem appears. Everytime I try to open a solution, it crashes! Rendering my VS2015 totally useless. It’s not a problem I can ignore, and I would avoid reinstall it, unless it’s the last resort.

Digging in the event viewers shed a light on what is wrong:

So System.Reflection.Metadata, Version=1.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ was missing.

But why? and missing from where? A quick search shows that it was used for Roslyn, and dnx uses version 1.1. Visual Studio 2015 also crashes if I try to open Tools => Text Editor => C# => Advanced, where the Roslyn options are. So the problem was Roslyn unable to load an assembly it needs, then the entire VS crashes. Lame!

It seems unable to just reinstall Roslyn alone, so my best bet is to put the correct System.Reflection.Metadata version … somewhere. It’s not clear that where should it be – so I should put it in the most common place – the GAC – Global Assemblies Cache.

This is an attempt to fix:

First, I need to have the assembly. This will require nuget 3.x to run, so you can download the latest version (3.4.4 at this time of writing) from here.

And then open your Developer command prompt for VS2015, and cd to the lib folder of downloaded package, for me it’s D:\Downloads\System.Reflection.Metadata.1.2.0\lib\netstandard1.1, and run the famous gacutil to install the assembly to GAC:

Open my solution again and it works! It might be not the best solution, but it works and I don’t have to reinstall VS2015, so I won’t complain.