Query custom content type performance issue

Five New Optimizely Certifications are Here! Validate your expertise and advance your career with our latest certification exams. Click here to find out more

AI OnAI Off

Home / Forums / Legacy forums / Episerver 7 CMS /

Query custom content type performance issue

Vincent

Vote:

We had Comment custom content type in our system that used with Topic page type. User allowed to add comments for each topic - similar to review or simple forum system.

However, if the topic has more than 100 comments, to query them for a specific topic is getting very very slow (approximate 50 seconds) for the first time with IContentRepository.

Can someone advise me how to fix this issue? Should I completely rewrite this using DynamicStore?

Environment

EPiServer 7.0
SQLServer 2008

Comment custom type declaration as follow

    public class Comment : IContent
    {
        public virtual string Body { get; set; }
       
        public virtual DateTime DateAddedInUTC { get; set; }
        public virtual DateTime DateUpdatedInUTC { get; set; }
        public virtual string UserId { get; set; }        
        public virtual bool IsFlagged { get; set; }
        public virtual string UserName { get; set; }   
        public virtual int Option { get; set; }
        public virtual bool IsHidden { get; set; }
       ................
    }

#88952

Aug 05, 2014 4:04

Johan Björnfot

Vote:

How are the Comment items stored. Are they children to the page? If so cant you load them with the GetChildren that has paging overload? Then you do not need to get them all at once.

#88999

Aug 05, 2014 16:18

Vincent

Vote:

Hi Johan

Thanks for your replay.

Yes, the comments are stored under against each page. I initially used GetChildren method, but I need to get the total number of comments for each topic and display it , therefore I can't use paging overload method.

Is there any efficient method to count the total comment from server side instead of client side?

#89021

Aug 06, 2014 2:28

Johan Björnfot

Vote:

You could use

IContentProviderManager.ProviderMap.GetProvider(contentLink).GetChildrenReferences(contentLink, languageID, -1, -1).Count

#89028

Aug 06, 2014 8:01

Vincent

Vote:

Thanks again for your promopt reply.

This project is built in 7.0, there is no generic version of GetChildrenReference method, any alternative?

#89034

Aug 06, 2014 8:33

Johan Björnfot

Vote:

Upgrade to 7.5 ;-)

Otherwise an option is to have a property CommentCount on the page it self that you update through eventhandlers to IContentEvents.CreatedContent and IContentEvents.DeletedContent (or perhaps a comment is never deleted??)

#89042

Aug 06, 2014 8:58

Vincent

Vote:

Thanks for your suggestion.

Yesterday I implemented the second option, but what I found was querying/updating the data in the DynamicStore is much more faster than update the page itself, therfore I switched to use the DynamicStore to save the total comments counter against topic id.

#89046

Aug 06, 2014 10:21

Vincent

Vote:

Hi Johan

It's me again.

Is there any known issue with GetChildren with pagination method in 7.0 (7.0.586.1)? See the attached sreenshot

https://www.dropbox.com/s/yufsbv5l7rflpqk/2014-08-07_095423.png

BTW: I am trying to attach the image, but it's always getting removed after I save the post

#89078

Edited, Aug 07, 2014 2:00

Johan Björnfot

Vote:

Yes you are correct that there is a bug in CMS 7 which migth occour if you use typed get children (with a generic argument different than IContent) together with paging. This has been fixed in CMS 7.5 (part of the ifx was as you saw earlier that it is now a typed GetChildrenReferences.

#89091

Aug 07, 2014 9:24

Vincent

Vote:

I don't know if the client is willing to pay extra money to upgrade. Is there any alternative or patch to fix this issue?

#89125

Aug 08, 2014 2:47

Johan Björnfot

Vote:

No, there is no patch for CMS 7 for this issue. The reason the fix has not been "backported" to CMS7 is that it requires quite large rewrites including database schema changes.

#89140

Aug 08, 2014 9:12

This thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.