Kentico 12 MVC, query and caching best practices

George Tselios asked on February 17, 2020 15:08

Dear sirs,

We are developing a new portal using Kentico MVC v12.0.51.

We have several questions regarding the best practices as far as document query and caching is concerned.

In the portal there is a footer section at the bottom of each page. The footer is consisting of 4 columns each of which displays a list of links. In the CMS, the footer is represented as a 3 level hierarchy. The first level is the actual footer tree node. Under the footer tree node we have the footer column tree nodes. Each footer column tree node contains any number of footer items (which are consisting of a title and a url).

In order to retrieve the entire content of the footer, we are examining the following approaches:

Create a single multidocument query using as Types all of the class names of the nodes that participate in the above hierarchy also specifying the appropriate NestingLevel. In addition, we specify the sorting to be by NodeLevel and NodeOrder. In this way with a single query we have all the nodes of the footer hierarchy in the correct order and then we process them accordingly. The downside here is that if a CMS author has created more than 4 footer column nodes then we load them as well, yet we do not display them.
First execute a DocumentQuery specifying the footer's ClassName. Once we have the footer tree node then execute another DocumentQuery specifying the footer columns' ClassName and also introduce a parentId filter that matches the footer node's Id. In this query we may specify a max count of 4 nodes to be returned. Finally, for each of the footer column tree nodes that the query returned, we introduce separate queries for the footer items under each footer column node. In this way we have at most 6 different queries so as to retrieve the entire footer's content. Since we deal with a specific Class Name at a time, we may introduce a specific caching statements for each query in order to optimize performance.
Use any of the above two approaches without introducing any caching statement and then use output caching in return (since the footer is displayed on all of the portal's pages).

Our question are:

If we wanted to introduce a caching statement for the first query, what would be the cache key and the dependency cache key?
In terms of document query (suppose we do not use caching) which approach of the first two is better in performance?
Considering caching, which of the three approaches would perform better?
In our portal, we also have other types of pages (like article pages) whose content is not stored on the actual node but it is distributed to its child nodes. In order to retrieve the entire content, we may proceed with one of the first two approaches since output caching may not be used here. In general, using a single query to retrieve all relevant nodes (in a hierarchy of 3 levels maximum) performs better than making the equivalent number of queries for each level of the hierarchy?

Thanks in advance,

George

mvc query cache best practices

Recent Answers

Peter Mogilnitski answered on February 17, 2020 16:40

I think the strategy is pretty much described in the example. I'd say use multi-document query to get all menu items in one shot and cache with appropriate dependency.

0 votesVote for this answer Mark as a Correct answer

George Tselios answered on February 17, 2020 17:26

Hi Peter,

Thanks for your response.

In our example we have three ClassNames (e.g. Footer, FooterColumn and FooterItem) that are used to form the footer hierarchy what type of dependency key should we use? Please note that the query only takes the class names, the sitename and the current culture as arguments no other information like NodeID is used.

Thanks in advance,

George

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on February 17, 2020 18:30 (last edited on February 17, 2020 18:49)

Well you can use multiple dependencies, but in your case i would use node|<site name>|<alias path>|childnodes (see documentation). What you want is whenever any child under /footer changes - flush the cache.

Your query should be something like:

var menu = DocumentHelper.GetDocuments()
    // type specific columns    
    .Type("FooterColumn", q => q
        .Columns("FooterColumnField1", "FooterColumnField2")
    .Type("FooterItem", q => q
        .Columns("FooterItemField1", "FooterItemField2")
    .Types("FooterColumn", "FooterItem")
    // shared columns
    .Columns("ClassName", "NodeID", "DocumentID", "NodeAliasPath", "NodeClassID", "NodeParentID", "NodeLevel", "DocumentName", "DocumentNamePath", "DocumentUrlPath", "DocumentCulture", "DocumentMenuRedirectUrl")
    .Path("/Footer", PathTypeEnum.Section)
    .OnSite(SiteContext.CurrentSiteName)
    .Culture(CurrentPageInfo.DocumentCulture)
    .Published()
    .OrderBy("NodeOrder", "NodeLevel")
    .TypedResult
    .AsEnumerable()
    .ToList();

Again I don't know your details, but that is pretty much the way I would do it.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 18, 2020 07:46

I just want to add that one of our MVPs created a nice tool for working with cache, called Cache Doctor. You may want to check it out as it could be handy when setting up caching.

0 votesVote for this answer Mark as a Correct answer

George Tselios answered on March 17, 2020 23:55

Dear All,

During this past time, we have done some debugging regarding the SQL statement that is generated by the DocumentHelper.

What we have found out is that for each type (ClassName) that we specify in the MultiDocumentQuery, the DocumentHelper emits a specific SQL block with two left outer joins. Moreover, each such SQL block is combined with each other using a UNION ALL statement.

In our example, if we use a MutliDocumentQuery for our three levels of the Footer structure then we end up executing an SQL statement with two UNION ALL and three blocks with two left outer joins, which seems slow on performance.

In addition, we have other similar (MutliDocument) queries that use the Path predicate which in addition to the UNION ALL and left outer joins introduce a LIKE operator in the generated SQL statement, which also is slow in terms of performance.

Is there any other way to construct a (MultiDocument) query with DocumentHelper that may alter the generated SQL statement in order to performs better?

Instead of using a single MutliDocument query, should we use two or three single document queries (as we described in our original post) since such queries will utilize the NodeID field which is an indexed field in order to expect better performance?

Thanks in advance,
George

0 votesVote for this answer Mark as a Correct answer