• Commerce,  Episerver,  Performance,  Uncategorized

    Fixing ASP.NET Membership performance – part 1

    Even though it is not the best identity management system in the .NET world, ASP.NET Membership provider is still fairly widely used, especially for systems that have been running for quite long time with a significant amount of users: migrating to a better system like AspNetIdentity does not comes cheap. However, built from early days of ASP.NET mean Membership provider has numerous significant limitations: beside the “architecture” problems, it also has limited performance. Depends on who you ask, the ultimate “maximum” number of customers that ASP.NET membership provider can handle ranges from 30.000 to 750.000. That does not sound great. Today if you start a new project, you should be…

  • Catalog,  Commerce,  Episerver,  Performance

    Speed up catalog routing if you have multiple children under catalog

    A normal catalog structure is like this: you have a few high level categories under the catalog, then each high level category has a few lower level categories under it, then each lower level category has their children, so on and so forth until you reach the leaves – catalog entries. However it is not uncommon that you have multiple children (categories and entries) directly under catalog. Even though that is not something you should do, it happens.  But that is not without drawbacks. You might notice it is slow to route to a product. It might not be visible to naked eyes, but if you use some decent profilers…

  • Catalog,  Commerce,  Episerver,  Order,  Performance

    Commerce batching performance – part 2: Loading prices and inventories

    UPDATE: When looked into it, I realize that I have a lazy loading collection of entry codes, so each test had to spent time to resolve the entry code(s) from the content links. That actually costs quite a lot of time, and therefore causing the performance tests to return incorrect results. That was corrected and the results are now updated. In previous post we talked about how loading orders in batch can actually improve your website performance, and we came to a conclusion that 1000-3000 orders per batch probably yields the best performance result. But orders are not the only thing you would need to load on your website. A…

  • Commerce,  Episerver,  Order,  Performance

    Commerce batching performance – part 1: Loading orders

    One of best practices for better performance – not just with Commerce or Episerver Commerce, is to batch your calls to load data. In theory, if you want to load a lot of data, loading by both end will be problematic: if you load each record one by one, the overhead for opening the connection and retrieve data will be too much. But if you load all of them, then it is likely that you will end up with either time out exception in database end, or out of memory exception in your application. The better way is to of course, loading them by smaller batch: either 10, 20, or…

  • Commerce,  Episerver,  Performance

    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…

  • Debugging,  Performance,  SQL Server,  Uncategorized

    Useful T-SQL snippets for development and troubleshooting

    This post is more of a note-to-self. These are the useful T-SQL statements which can be incredibly useful in development and troubleshooting [crayon-5ce282ecce4ea716221579/] Turn on the IO statistics for statements run after that until set to OFF explicitly. We then switch to Messages tab to see how many IO operations were done on each table. [crayon-5ce282ecce4f5606980178/] Find out about the statements were executed: which statements, its texts, how many reads (logical), how many time was spent on CPU and how many time was spent total

  • Catalog,  Commerce,  Episerver,  Performance,  Tips

    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.

  • 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-5ce282eccf74f903938285/]This query is known to be slow. The…

  • Commerce,  Episerver,  Order,  Performance,  SQL Server

    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: [crayon-5ce282ecd01ce084802284/] 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. [crayon-5ce282ecd01d7165176863/] Because ObjectId is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index…