Optimize DDS linq query.

Vote:
 

HI,

I need suggestions to optimize below linq query.

store.Items<StorageMessage>().ToList()
.Where(x => x.Recipients.Select(y => y.Code).Contains(testCode))
.OrderByDescending(x => x.DateCreated)
.ToList();

the use of ToList() is affecting performance and sometimes results in timeout error.

we tried to apply where condition on store directly but getting different results.

Any Suggestions? 

#216995
Feb 12, 2020 11:41
Vote:
 

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; }
#216997
Feb 12, 2020 12:12
Vote:
 

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

#216998
Feb 12, 2020 12:14
Vote:
 

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/ 

#217004
Feb 12, 2020 13:58
Vote:
 

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?

#218073
Edited, Mar 05, 2020 13:15
Vote:
 

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?

#218104
Mar 06, 2020 6:08
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.