• 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…

  • 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…

  • 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-5c06d8d9b2150231633157/] 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-5c06d8d9b2158869805829/] Because ObjectId¬†is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index…

  • CMS,  Commerce,  Episerver,  Performance,  SQL Server,  Uncategorized

    Maintaining your indexes

    Indexes are crucial to SQL Server performance. Having the right indexes might make the difference of day and night with your application performance – as I once talked here. However, even having the right indexes is not everything. You have to keep them healthy. Indexes, as any other kinds of storage, is subjected to fragmentation. SQL Server works best if the index structure is compact and continuous, but with all of the inserts/updates/deletes, it’s inevitable to get fragmented. When the fragmentation grows, it starts affecting the performance of SQL Server: Instead of having to read just one page, it now have to read two, which increases both time and resource…

  • Commerce,  Episerver,  Performance,  SQL Server

    Watch your indexes closely

    Recently we were tasked to help a customer having a problem with a query. This specific query ate a lot of CPU resources (30-40%) and causing performance problem for other queries – as it slows the entire SQL Server instance down. Upon investigation, we discovered that the query was accessing a table with an outdated index. The index was supposedly updated in Episerver Commerce 7.10.3, which was released almost 3 years ago. For some reasons, the index was not updated in customer’s table. Instead of just having to do a index seek, SQL Server was forced to do a full table scan, which is much slower, causing the problem. If…