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.

One of those places are contact information.

Commerce, allows you to extend customer contact information by adding more fields into it, as with other Business Foundation objects. One fairly common scenario in Sweden is you probably want to add personnummer (personal number, which is the general “Social Security Number” for every registered person in Sweden, it is, however, much less “secret” than the SSN in the US, you are quite free to share personnummer with other people without much trouble) to a contact. Naturally you would want to filter the contact by their personnummer.

Business Foundation is smart enough to build a query to get the contact by this WHERE condition:

(@Param0 nvarchar(9))SELECT TOP 5000 [t01].[ContactId] AS [ContactId], 
[t01].[Created] AS [Created], [t01].[Modified] AS [Modified], 
[t01].[CreatorId] AS [CreatorId], [t01].[ModifierId] AS [ModifierId], 
[t01].[FullName] AS [FullName], [t01].[LastName] AS [LastName], 
[t01].[FirstName] AS [FirstName], [t01].[MiddleName] AS [MiddleName], ... 
[t01].[Personnummer] AS [Personnummer]
FROM [cls_Contact] AS [t01]
WHERE ([t01].[Personnummer]=@Param0)

It does not need an expertise on SQL Server to know that a NON CLUSTERED INDEX on Personnummer column would help this query greatly. The only problem is Episerver does not know about this column, nor how is it used, so it’s impossible to add an index to this column from a framework perspective. Adding the index would fall into your hand now.

Remember, adding an index is not without drawbacks, so make sure the index you are adding is a good index:

• It should be on a column with a good selectivity, which means, in simple term, distinctive values. In best case, the values should be unique, but a few duplicated values would not hurt. It is not a good idea, however, to add an index to column with only 5-6 distinct values. This also means that adding index to a column in a very small table would not help that much
• It should be used to query data. For example, the above query actively uses the index on Personnummer column to query data. If you only query by email, for example, adding index to Personnummer , even it’s unique, would not help.

And then should you go ahead and add indexes to every column possible? Unfortunately, no. Then there are cases when an additional index can cause a lot of trouble.

We had a ticket recently when a customer has done quite intensive profiling themselves and they figured out they should add some more indexes to some columns in tblBigTable. Sounds reasonable? But that actually took their site down with this error message:

Remapping can be done by applying the EPiServer.Data.Dynamic.EPiServerDataStoreAttribute attribute to the type,
setting its AutomaticallyRemapStore property to true and ensuring the <episerver.dataStore><dataStore> autoRemapStores attribute in web.config is set to true (or is not defined).

Because they had done some other changes themselves (and never mentioned these additional indexes to us), it took us quite some time (and many hairs) to figure out by adding those indexes, the mapping between database schema and the model type no longer match. Dynamic Data Store is very sensitive and strict about those mappings, so it complains when it finds an index when it should not. Lesson learned: when you add a custom index yourself, make sure to document it and take it step by step, so you won’t accidentally take your site down.

And as always, if you are not so sure if you should add this or that index, Episerver developer support service is available to help.