C#, SQL, Episerver/Optimizely, performance, and beyond
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.
Continue reading “Please, rebuild your database indexes, now”
Well, that was the title of my talk at the last week Meetup in Lund – hosted by Avensia. My first time in Lund – it is a very beautiful (small) city. It was a very nice event with about thirty developers from inside and outside Avensia, and I hope I did give some good information to make your Commerce solutions faster!
Then it comes to my attention that some of you might be interested in the talk. Unfortunately I don’t think the talk was recorded (I’d have been famous on Youtube!), but here’s the slides from the talk:
Continue reading “Super charged Episerver Commerce performance”
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”
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/
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”
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.
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”
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?”
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”
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)
SELECT CatalogNodeId, Count(CatalogEntryId) FROM dbo.NodeEntryRelation GROUP BY CatalogNodeId ORDER BY Count(CatalogEntryId) DESC
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.