How many properties does your StorageMessage class contain? If the columns in the tblBigTable is not enough, it will split each entry into two or more rows in the table. This affects performance in a very negative way. You can actually extend this table yourself by adding new columns when needed. If you for example have 11 string properties in your class, you can add a new column named String11 (NVARCHAR(MAX)). I have optimized a feature on a site which took ages to load. When adding all the columns needed and migrating the existing data into only one row in the table, it was down on milliseconds to load again.

This is a list of default number of columns in tblBigTable:

• Boolean01-05
• Integer01-10
• Long01-05
• DateTime01-05
• Guid01-03
• Float01-07
• Decimal01-02
• String01-10
• Binary01-05
• Indexed_Boolean01
• Indexed_Integer01-03
• Indexed_Long01-02
• Indexed_DateTime01
• Indexed_Guid01
• Indexed_Float01-03
• Indexed_Decimal01
• Indexed_String01-03
• Indexed_Binary01

You can also mark some properties with EPiServerDataIndex attribute to achieve faster filtering:

[EPiServerDataIndex]
public string MyString { get; set; }

However, I would recommend migrating to Entity Framework. :)

You should not have to use ToList() here store.Items<StorageMessage>().ToList()

And yes, DDS is slow by design and should be avoided if you have considerable amount of items https://vimvq1987.com/dynamic-data-store-is-slow-but-you-can-do-better/ 

Hi Mattias,

we have less than 10 string properties. This is how our model is structured. We have one collection type property which is getting stored in [tblBigTableReference] table

internal class Message : IDynamicData
{
public Message()
{
Teachers= new List<Teachers>();
}

public Identity Id { get; set; }

public List<Teachers> Teachers{ get; set; }

public string Subject { get; set; }

public string Body { get; set; }

public DateTime DateCreated { get; set; }

public Guid Sender { get; set; }

public string SenderImageUrl { get; set; }

public string SenderInitials { get; set; }

public string SenderName { get; set; }

public int SenderContentId { get; set; }
}
}

public class Teachers
{
public string Name { get; set; }

public string code{ get; set; }
}

However, seems like tblBigTable is spliting rows because String01 column is unable to hold complete data which is a HTML string.

I am trying to get a List of Messages based on Teachers code.

Any Suggestion?

Sorry, no more suggestions other than migrating to Entity framework, especially since you have a collection type property. DDS is simply not designed for these purposes.

I wish that Episerver never told anyone about it from the beginning. :)

However, Quan is right that you should not have to use .ToList here. What was the reason for that again?