• Catalog,  Commerce,  Episerver,  Performance,  Tips,  Uncategorized

    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…

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    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: [crayon-5c439bc29f3b0161194971/]This query is known to be slow. The…

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    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.

  • Catalog,  Commerce,  Episerver,  Tips

    Read only Catalog UI – part 1

    https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2015/6/read-only-catalog/ 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.

  • Catalog,  Commerce,  Episerver,  Tips

    Beware of IContentLoader.GetChildren() for CatalogContent

    This is more of a self-to-note. If you are using IContentLoader.GetChildren<T>(ContentReference), one important thing to remember is this uses the current preferred language. Normally when you get children of a catalog, or a node, that would not be a problem, because a catalog entity – node or entry, will be available in every language supported by the catalog. So if you just want to get the children references, the language is not important. (Note that, if you just need the children references, IRelationRepository should be a faster, more lightweight way to go, but that’s another story). If you want to get children in a specific language – which is the most common…

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    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/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,…

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    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/

  • Catalog,  Commerce,  Episerver,  Performance,  Tips

    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…

  • Catalog,  Commerce,  Episerver,  Tips

    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?

  • Catalog,  Commerce,  Episerver

    Permanently drop prices of all products

    This is an unusual post – as I usually don’t post sample code – that should be the job of the documentation. However, I jumped upon this question http://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/8/global-price-increase/, and found it to be an interesting case to demo. It’s worth noting that as a customer, I’d like price drops, not the way around, so in this example, we will see how to cut prices of all products to 5%, instead of making them 5% more. Of course, it’s just simple mathematics, so you can change to the formula however you want.