Speed up your catalog indexing performance – part 2

Almost two years ago I wrote part 1 here: https://vimvq1987.com/speed-catalog-entries-indexing/ on how to speed up your catalog indexing performance. If you have a fairly big catalog with frequent changes, it might take longer time than necessary to build the index incrementally. (Rebuild index, in other hands, just delete everything and rebuild from scratch, so it is not affected by the long queue in ApplicationLog). I have seen some cases where rebuilding the entire index, is actually faster than waiting for it to build incrementally.

The tip in previous blog post should work very well if you are using anything lower than Commerce 11.6, but that is no longer the case!

Continue reading “Speed up your catalog indexing performance – part 2” →

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.

Continue reading “Index or no index, that’s the question” →

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.

Continue reading “Speed up your Catalog incremental indexing” →

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:

declare @s [udttIdTable]
insert into @s values(6)
exec dbo.ecfVersion_ListFiltered @Statuses = @s, @StartIndex = 0, @MaxRows = 2147483646

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

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

Lessons learned from a “boring” Black Friday

This Black Friday, I was in a task force, ready to help high profile DXC Commerce customers to cope with a peak in traffic. It turned out to be boring – there was no moment I could be a hero and “save the day”. Things went surprisingly smooth, even for websites which were struggling with last Black Friday. I went home and was little dissatisfied, but deep down, I know it was a huge success. The hard work from us (Commerce development team), them (the partner development teams), and Managed Services has been well paid off.

Use the latest version

You have heard me saying this, and you will probably hear from me again: Please, use the latest version possible. There is no reason to stay with an old version. This is a little truth: I hurt a little deep inside whenever I read a question on world asking a question about Commerce 7.5, or even 8.x. You should be using at least 10.8 by now, if not 11.x.

Continue reading “Lessons learned from a “boring” Black Friday” →

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