Tag: Commerce

A curious case of SQL execution plan, part 2

Recently I wrote about how to look into, identify and solve the problem with a SQL Server execution plan – as you can read here: http://vimvq1987.com/curious-case-sql-execution-plan/

I have some more time to revisit the query now, and I realized I made a “small” mistake. The “optimized” query is using a Clustered Index Scan

So it’s not as fast as it should be, and it will perform quite poorly in no cache scenario (when the buffer is empty, for example) – it takes about 40s to complete. Yes it’s still better than the original one, both in non cached and cached cases. But it’s not good enough. An index scan, even cached, is not only slower, but also more prone to deadlocks. It’s also worse in best case scenario, when the original one can use the proper index seek.

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

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” →

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” →

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” →

Multisite in Episerver Commerce

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” →

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 ) SKUs. Will it work?”

Definitely, maybe.

I’ve seen “big” catalogs. Some very big. Million and more products.

The correct term would be “Entries”, as in a standard implementation your catalog can contain products, variants (of those products, or standalone ones), bundle and package. However for the sake of simplicity, we will refer to them as “products”. So when we talk about 2 million products, it is the 2 million entries. (you can have 100k products, 1.800k variants and 100k packages)

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?” →

Watch your indexes closely

Recently we were tasked to help a customer having a problem with a query. This specific query ate a lot of CPU resources (30-40%) and causing performance problem for other queries – as it slows the entire SQL Server instance down.

Upon investigation, we discovered that the query was accessing a table with an outdated index. The index was supposedly updated in Episerver Commerce 7.10.3, which was released almost 3 years ago.

For some reasons, the index was not updated in customer’s table. Instead of just having to do a index seek, SQL Server was forced to do a full table scan, which is much slower, causing the problem.

If you want to go into details, it’s mdpsp_getchildrenbysegment stored procedure, which looks into UriSegment column of CatalogItemSeo table, previously, the index was like this:

CREATE NONCLUSTERED INDEX [IX_CatalogItemSeo_UniqueSegment_CatalogEntry] ON [dbo].[CatalogItemSeo]
(
    [ApplicationId] ASC
    [UriSegment] ASC,
    [CatalogEntryId] ASC
)

You can see the problem: The order of the index was bad – because ApplicationId was not distinctive (in fact, in most of the cases it’s the same for every row), and because UriSegment was not the first column in the index, this index will not be used if a query uses UriSegment only. Continue reading “Watch your indexes closely” →

Quicksilver + ServiceAPI: the authentication issues

It’s possible to run Quicksilver and ServiceAPI on a same site, with some modifications, as I blogged here. However, if you go down that path, there is something you must keep in mind: They are not using the same authentication mechanism.

I’ve seen issues where Quicksilver implementations have some WebAPI controllers, which were working fine until ServiceAPI is installed. The controllers started returning null for CustomerContext.Current.CustomerContact, and so on, breaking some functionalities. It’s bad, yes, but it happens because of reasons.

Continue reading “Quicksilver + ServiceAPI: the authentication issues” →