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
* 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.