We are having a problem with Episerver DDS objects. About 30 000 objects is stored in dds and when I am trying to search through them (ex. orgBase.Items().Where) it takes too much time. Episerver data indexing is used ([EPiServerDataIndex]). Is there any way to optimize search time? Or any other technologies how to store/search for objects (maybe IContent)?
You could try to use Entity Framework. I found the article about the performance of DDS and EF: http://robertlinde.se/post/performance-of-episerver-dds-vs-entity-framework
we found that with record acount around 10k DDS starts to perform not well.. EF could be an alternative. Depends on your requirements..
If you can make sure every object takes up one single row in the database and avoid having complex child objects it'll be faster I think. You know you can extend the tblBigTable with your own columns to avoid multiple rows? Just add Boolean06, String11, Indexed_String04 etc (make sure you create a SQL script to do it in all your environments). If you add new columns you would have to migrate or rebuild the store in some way. Also go through your query code and see if you can make any optimizations there. Don't call the execute methods (ToList, Count etc) more than needed, filter as much as possible before etc etc.
I optimized like this in a project and narrowed the query time down from like 5-10 seconds to just milliseconds. However, if you can migrate to Entity Framework, NHibernate or something similar I think it will be more maintainable and future proof.
Thanks for all replies. I decided to stick on using DDS (too late to change architecture, but will not definetely choose this technology nex time). DDS indexing doesnt help much. I am just using pagination in sql request and base.Items<>() instead of base.LoadAll<>(). It works slowly with complex objects, unfortunately I cant avoid using them.
@Mathias: Not quite understand what you mean with Boolean06, String11, Indexed_String04. Could you make an example?
This might help with understanding what @Mattias means by adding columns.