Commerce

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. (more…)

By vimvq1987, ago
Commerce

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-5b09162e729de090651443/] 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-5b09162e729e6322234971/] Because 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. (more…)

By vimvq1987, ago
CMS

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 needed, and so on and so forth. (more…)

By vimvq1987, ago
Commerce

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 you want to go into details, it's mdpsp_getchildrenbysegment stored procedure, which looks into UriSegment column of CatalogItemSeo table, previously, the index was like this: [crayon-5b09162e735f8586892449/] You can see the problem: The order of the index was bad - because ApplicationId was not distinctive (in fact, in most of the cases it's the same for every row), and because UriSegment was not the first column in the index, this index will not be used if a query uses UriSegment only. (more…)

By vimvq1987, ago