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.

Continue reading “Mass update catalog entries”

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:

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.

Continue reading “A curious case of SQL Server function”

Optimizing T-SQL COUNT

This is a continuation of my previous post about paging in SQL Server. When it comes to paging, you would naturally want to know the total number of rows satisfying, so you can display some nice, useful information to your end-users.

You would think, well, it’s just a count, and a simple query like this would be enough:

There should be nothing to worry about, right? Actually, there is.

Let’s get back to the example in previous post – we have to count the total number of orders in that big table.

Because ObjectId is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index and be pretty fast. But does it? To my surprises, no.

Continue reading “Optimizing T-SQL COUNT”

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.

Continue reading “The art of paging”

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.

Continue reading “Fixing a stored procedure”

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.

Continue reading “Please, rebuild your database indexes, now”

Super charged Episerver Commerce performance

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”

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: https://vimvq1987.com/2017/10/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, and I will repeat it here: SQL Server optimizer is smart, and I can even say that, generally, 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/

(As a side note, I don’t answer direct questions, nor provide any personal support service (I would charge plenty for that 😉 ). I would appreciate if you go through World forums, or contact Episerver Developer support service. There are several reasons for that, including knowledge sharing, and work item tracking. I can make exceptions when I know the problem is highly urgent and is hurting your business, by jumping into it sooner than I’m expected to/before it’s escalated through several level of supports. But all in all, it should be registered with Deverloper support. We at development team are supposed to be the final line of support, not the front line. )

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”