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”
This time, we will talk about
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”
UPDATE 1: Apparently
HttpContext.Current.Request.AnonymousID already uses the cookie internally, so there might be something that makes it stop working. I’ll update when I found out.
Today we received a support ticket as customers seeing corrupted carts data being lost – line items with invalid data, duplicated line items etc. “Corrupted data” is one of the alarming words that we take very seriously, so I decided to jump on it right away.
The setup is a load balancing environment, and the problem only happens with anonymous users. However, it can be “fixed” by turning on the sticky sessions mode. So basically, instead of having sessions on the memory of a server (so sessions on server A can’t be seen by server B, and vice versa), they need a mechanism (can be a database) to share sessions between servers.
Continue reading “Loading carts in a load balancing environment”
More than one year ago, I announced that I was working on a book – a first Episerver Commerce book ever. It has been a work in progress until recently – and I am still adding updates here and there. The book has received quite positive feedback (I’m happy to say that everyone is nice enough to not tell me “Your book sucks”. Thanks, everyone). Am I happy with it? Yes, of course, proud even.
But to be completely honest,
I know something was missing.
Continue reading “Announcing a new book: Episerver Commerce: A problem – solution approach”
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:
SELECT COUNT(Id) FROM MySecretTable
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.
SELECT COUNT(ObjectId) FROM OrderGroup_PurchaseOrder
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”
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”
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”
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”
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”
A while back, we had this question on World. It’s not uncommon to update the catalog data by an external system, mostly from a PIM – Product information management system. In such cases, it might not make senses to enable editing in Catalog UI. You might need the new UI for the other parts, such as Marketing UI, but you wouldn’t want the editors to accidentally update the product information – because those would be lost, anyway.
Is there away to do it? Yes, there is.
Continue reading “Read only Catalog UI – part 1”