In the first two parts of this series, I wrote about how you can store your data using Dynamic Data Store (Part I) and how Episerver implemented DDS (Part II). Now we’ll learn about a more effective way of storing your data in Dynamic Data Store.

By default, Episerver keeps data of all stores in one big table (tblBigTable) which, as you may guess, isn’t very good for performance. If you search for data in one store, then the code actually needs to filter all other entries from other stores in the big table. To make the code more efficient, you should have a separate table in your database for each Dynamic Data Store.

Creating a custom table

To demonstrate the solution, let’s use an example from Part I – let’s store views of pages with CustomTablePageViewsData class:

using EPiServer.Data;
using EPiServer.Data.Dynamic;
using System;

namespace Setapp.DataStore
{
    [EPiServerDataStore(AutomaticallyCreateStore = true, AutomaticallyRemapStore = true)]
    public class CustomTablePageViewsData
    {

        public Identity Id { get; set; }

        [EPiServerDataIndex]
        [EPiServerDataColumn(ColumnName = "PageId")]

        public int PageId { get; set; }

        [EPiServerDataColumn(ColumnName = "ViewsAmount")]
        public int ViewsAmount { get; set; }
    }
}

Obviously, first you need to create your own table in the database. The best thing to do is to create it automatically from your code on an application startup. You can create a class which implements IInitializableModule interface and set its dependency on DataInitialization class. You will also need a database connection which you can get by calling:

var databaseHandler = ServiceLocator.Current.GetInstance<IDatabaseHandler>();
using (var connection = new SqlConnection(databaseHandler.ConnectionSettings.ConnectionString))
{
    connection.Open();
}

So here’s the class that you should have so far:

using EPiServer.Data;
using EPiServer.Framework;
using EPiServer.Framework.Initialization;
using System.Configuration;
using System.Data.SqlClient;

namespace Setapp.DataStore
{
    [InitializableModule]
    [ModuleDependency(typeof(DataInitialization))]
    public class CustomBigTableInitializer : IInitializableModule
    {
        private const string ConnectionStringName = "EPiServerDB";

        public void Initialize(InitializationEngine initializationEngine)
        {
            var databaseHandler = ServiceLocator.Current.GetInstance();
            using (var connection = new SqlConnection(databaseHandler.ConnectionSettings.ConnectionString))
            {
                connection.Open();
            }

        }

        public void Uninitialize(InitializationEngine initializationEngine)
        {
        }
    }
}

Here’s a general string pattern that can be used to generate an SQL query to generate any table for DDS:

private const string CreateTableSql = @"if OBJECT_ID('dbo.{0}', 'U') is null 
    CREATE TABLE [dbo].[{0}] 
    ([pkId] bigint NOT NULL, 
    [Row] int NOT NULL default(1) CONSTRAINT CH_{0} CHECK ([Row]>=1), 
    [StoreName] nvarchar(128) NOT NULL, 
    [ItemType] nvarchar(512) NOT NULL, 
    {1}
    CONSTRAINT [PK_{0}] PRIMARY KEY clustered([pkId],[Row]), 
    CONSTRAINT [FK_{0}_tblBigTableIdentity] FOREIGN KEY ([pkId])
    REFERENCES [tblBigTableIdentity]([pkId])); ";

{0} is where the name of the table goes, for example tblPageViewsData.
{1} is the place for a list of columns which correspond to public properties in a object that is supposed to be stored. In our example we would need to create the following list:

[PageId] int null,  
[ViewsAmount] int null,

Column names need to correspond to the names used in EPiServerDataColumn attribute in CustomTablePageViewsData class. If you skip this attribute, you will need a default Episerver naming convention. In our case you’d need to use the names Integer01 and Integer02. That’s just because we have two integer properties in PageViewsData (PageId and ViewsAmount). If you had another property which is a string, then you’d have to create a column called String01 etc. The naming convention is described more precisely in Part II in DDS class mapping section.

What about the rest of the query? At the beginning you need to check if the table is not created yet. For a DDS table you always need a few more columns: pkId, Row, StoreName and ItemType (their purpose is also described in Part II). Note that in the case of Row column, if you map all properties of the class to appropriate columns, then it will always contain a value 1. And that’s what you want to achieve, because this way one object won’t be split into multiple rows in the database.

Your custom table also requires a primary key on [pkId] and [Row] columns as well as a foreign key which references a primary key column pkId in tblBigTableIdentity table.

Adding indexes to your table

You can also create indexes on chosen columns and their name does not need to have a prefix Indexed_. Here’s an example pattern string for creating an index:

@" IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_{0}_{1}')
    CREATE NONCLUSTERED INDEX [IDX_{0}_{1}] 
    ON [dbo].[{0}]([{1}]) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); "

{0} is the name of a table.
{1} is the name of a column you want to put an index on.

Let’s create a DynamicDataStoreSqlProvider class which will generate an SQL query for creating the table. Here’s how the whole class looks:

using System.Collections.Generic;
using System.Text;

namespace Setapp.DataStore
{
    public class DynamicDataStoreSqlProvider
    {
        private const string CreateTableSql = @"if OBJECT_ID('dbo.{0}', 'U') is null 
        create table [dbo].[{0}] 
        ([pkId] bigint not null, 
        [Row] int not null default(1) constraint CH_{0} check ([Row]>=1), 
        [StoreName] nvarchar(128) not null, 
        [ItemType] nvarchar(512) not null, 
        {1}
        constraint [PK_{0}] primary key clustered([pkId],[Row]), 
        constraint [FK_{0}_tblBigTableIdentity] foreign key ([pkId])
        references [tblBigTableIdentity]([pkId])); ";

        public string GetCreateTableSql(string tableName, string sqlTableColumns, string storageName, IEnumerable<IEnumerable> sqlCreateIndex)
        {
            return string.Format(CreateTableSql, tableName, sqlTableColumns) + GetCreateIndexSql(tableName, sqlCreateIndex);
        }

        private string GetCreateIndexSql(string tableName, IEnumerable<IEnumerable> sqlCreateIndex)
        {
            var stringBuilder = new StringBuilder();
            foreach (IEnumerable indexColumns in sqlCreateIndex)
            {
                foreach (string indexColumn in indexColumns)
                {
                    stringBuilder.Append(GetIndexCreationQuery(tableName, indexColumn));
                }
            }

            return stringBuilder.ToString();
        }

        private string GetIndexCreationQuery(string tableStorageName, string columnName)
        {
            return GetIndexCreationQueryWithReadyColumnsNames(tableStorageName, columnName);
        }

        private string GetIndexCreationQueryWithReadyColumnsNames(string tableStorageName, string columnNamesForIndexName)
        {
            return string.Format(
                @" IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_{0}_{1}')
                    CREATE NONCLUSTERED INDEX [IDX_{0}_{1}] 
                    ON [dbo].[{0}]([{1}]) 
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); ",
                tableStorageName,
                columnNamesForIndexName);
        }
    }
}

Connecting a custom table to Dynamic Data Store

Once you create the table, you have to tell Episerver to connect it with the store of your object. First you need to assign the name of the store to a type of object:

if (GlobalTypeToStoreMap.Instance.ContainsKey(ObjectType))
{
    GlobalTypeToStoreMap.Instance.Remove(ObjectType);
}

GlobalTypeToStoreMap.Instance.Add(ObjectType, StoreName)

Then you need to assign the table name to a definition of the store:

var parameters = new StoreDefinitionParameters
{
    TableName = TableName,
};

GlobalStoreDefinitionParametersMap.Instance.Add(StoreName, parameters);

And that’s all you need to do! Episerver will handle the rest and create a store on a first call of:

typeof(CustomTablePageViewsData).GetOrCreateStore();

or

typeof(PageViewsData).CreateStore();

Then you can use your store normally as you would with it being kept in the default big table.

Optimization results

I created two classes for page views data with identical properties as in the example above. One called DefaultTablePageViewsData stored in the default big table and CustomTablePageViewsData which is stored in a separate custom table. Additionally I created another class FakeData which is also stored in the default big table. This is to simulate having more stores in the default table.

I added 50 000 objects to each store (including FakeData), filling ViewsAmount properties from 1 to 50 000. Then I measured the time for retrieving an object with ViewsAmount equal to 25 000:

typeof(DefaultTablePageViewsData).GetOrCreateStore()
      .Items<DefaultTablePageViewsData>()
      .FirstOrDefault(item => item.ViewsAmount == 25000);

typeof(CustomTablePageViewsData ).GetOrCreateStore()
      .Items<CustomTablePageViewsData >()
      .FirstOrDefault(item => item.ViewsAmount == 25000);

Here’s the average time (in milliseconds) to get the values for both cases:

As you can see, retrieving data from a store in the default big table takes 3 times longer in this case! No wonder! As there is twice as much data in the table. And that’s quite an optimistic scenario as normally you’d probably think it’s fine to create each store in the same big table which would grow and grow and grow…

Even if we get rid of the FakeData store (so both the default big table and the custom table contain the same amount of rows!), the test shows that getting data from the default big table takes twice as much time!

I also compared times of adding the 50 000 rows to DefaultTablePageViewsData and CustomTablePageViewsData stores, but I omitted adding rows to the FakeData store. It would seem that there shouldn’t be a big difference since both your custom table and the default big table are empty. But unfortunately that’s not the case.

As you can see filling the store in the default big table takes about 25% longer!

Conclusion

As you can see it’s worth the effort to separate your stores in the database. It will definitely boost the performance of your website. Here’s the whole example project to download, including performance tests. You can run the tests and see the results yourself by going to yourdomain/performance-test. It can take about 20 minutes before it finishes running and the page turns on so be patient.

So are we there yet? Is this the best way to store our custom data in an Episerver application? Surely not and in Part IV you’ll learn an easy alternative to DDS which is faster and more flexible. Coming soon!

P.S. At Setapp, we are a team of talented and experienced.Net developers working on Episerver and other CMS solutions. If you’d like to be part of our growing team, get in touch with us!