Large Query on page load

Alistair DeJonge asked on March 3, 2017 16:22

I'm trying to find the source of a very large query that occurs during the initial page load of every page on my site. It looking like it querying all content and UNIONing it over and over and over again. Is this a necessary query? Can it be prevented? How? Does it have anything to do with the portal type template?

Any help would be much appreciated. Thanks!

Recent Answers


Zach Perry answered on March 3, 2017 16:25

Have you turned on SQL debugging to figure out what query is running? Once you do that, you can find out what is making the query and then decide if it is needed or not.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on March 3, 2017 16:33 (last edited on March 3, 2017 16:34)

Massive UNIONing might be the result of using .WithAllData - it runs the query for all page types defined in your CMS. Turn on SQL live debugging like Zachary suggests, so you can see SQL queries produced by each page: Image Text Start with your master page.

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on March 3, 2017 16:57

In addition to what Peter said you might need to specify content types for any listing/viewer/data source web part on your page.

If system runs that query for every page - most likely the source of that query sits on the master page, for example navigation.

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on March 3, 2017 17:01

I have turned it on, that is how I found it. We have optimized web parts by specifying page types and columns.

I need to use WithAllData, because I need the data from other page types in certain transformations, but I specify ClassNames and Children under specific Documents.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 3, 2017 17:04

You might want to look into another solution then if you're using the WithAllData macro, especially if you're using it in a repeater's transformations or something similar. You might post what is causing the large queries and what you're looking to do.

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on March 3, 2017 17:19 (last edited on December 10, 2019 02:30)

When I take away WithAllData, things stop working... for example, I have a transformation that applies another page type's transformation to all the children of one type in a folder:

{% Documents[NodeAliasPath].AllChildren.ClassNames("custom.UniversalSlider").WithAllData.ApplyTransformation("custom.UniversalSlider.Slide") |(identity)GlobalAdministrator%}

The custom.UniversalSlider.Slide transformation just has the field name macros in it based on the content entered by the client. The result is x amount of divs based on the number of Slideritems.

If I remove the WithAllData it does not work.

But all of that is beside the point, because i don't even know if that is the result of the Large Query running 11 times on on page.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 3, 2017 17:21

Sounds like you're using a repeater with a nested repeater/query. Better off using the universal viewer for something like this. It will allow you to set levels and such very easy.

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on March 3, 2017 18:05

I have tried using the Universal Viewer, but it's not showing any content.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 3, 2017 18:07

Make sure you check the box to "Load hierarchical data" as well as making sure your levels are set properly in the hierarchical transformation.

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on March 3, 2017 18:15

The other problem is, the viewer is not going to work as a replacement in most cases, because the applying of transformations to children is based on currentdocument field data.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on March 3, 2017 18:16

I would go with custom macro that returns IEnumerable here and used inside kentico document query api

DocumentHelper.GetDocuments("custom.UniversalSlider").... - this for sure gets the documents of your class

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 3, 2017 18:19

This will work Peter although if you're calling this in each transformation, it's no better than what he had before. If the custom code is not properly implemented (unnecessary loops, no caching, etc.), again, no better than what he had before.

Use the Universal Viewer, properly configured. No need to write code for this.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on March 3, 2017 19:21 (last edited on December 10, 2019 02:30)

Brenden, not really, here is how I would do it ... You have custom macro (You can cache it if you want to) {% GetSliderData("custom.UniversalSlider",CurrentDocument.NodeID).ApplyTransformation("custom.UniversalSlider.Slide") |(identity)GlobalAdministrator%}

Yes it is more code but you get more control.

[MacroMethod(typeof(object), "GetSliderData", 2)]
[MacroMethodParam(0, "ClassName", typeof(string), "ClassName")]
[MacroMethodParam(1, "NodeId", typeof(int), "Node Parent Id")]
public static object GetSliderData(EvaluationContext context, params object[] parameters)
{
    return GetSliderDataInternal(ValidationHelper.GetString(parameters[0], ""), ValidationHelper.GetInteger(parameters[1], ""));
}

private static IEnumerable<DataRow> GetSliderDataInternal(string className, int nodeId)
{
    InfoDataSet<TreeNode> result = null;
    var lang = DocumentContext.CurrentDocumentCulture.CultureCode;
    string cacheKey = className +  "|All";

    using (
        CachedSection<InfoDataSet<TreeNode>> css = new CachedSection<InfoDataSet<TreeNode>>(ref result,
            CacheProvider.cacheInterval(), true, null, cacheKey))
    {

        if (css.LoadData)
        {
            // 1. You can put Where here or down below.
            result = DocumentHelper.GetDocuments(className).WhereEquals("NodeParentID", nodeId).TypedResult;

            if (css.Cached)
            {
                css.CacheDependency =
                    CacheHelper.GetCacheDependency(new[]
                    {
                       cacheKey
                    });
            }

            css.Data = result;


        }
    }
    return result != null && result.Items.Count > 0
        ? result.Tables[0].AsEnumerable()
        // 2. Or you can put WHERE condtion here Where(x => x.Field<int>("NodeParentID") == nodeId)  
        : new DataTable().AsEnumerable();
}

I didn't compile this. It might be a mistake in there:)

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on March 6, 2017 15:23

The only issue with the Universal Viewer is on version 8.2, it loaded in EVERY field regardless if you set a Columns, because it didn't have logic to select multiple page types and properly union them.

In version 9, they got it to where you could filter by the columns, but it will throw an error if you try a Where condition that uses a field that not all of them contain.

I did create an Optimized Hierarchy Viewer that gives the best of both worlds, i never got a chance to convert it to all the main Kentico versions yet. If the above didn't resolve the issue, tell me and i'll try to get it up for you.

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on March 7, 2017 13:12

Did you look into this performance document? it will checklist against all potential issues.

Kentico Performance

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on March 7, 2017 16:05

Thank you all for your help. The issue was with WithAllData, but not specifically in the slides... it was everywhere. I have optimized it everywhere I could find it by either replacing it with a Documents[NodeAliasPath], or using WithAllData with Columns. If the query was in a for loop, I built a string to use in a single query with a Where clause, then putting the results in an array of objects and iterating through it.

Funny enough, the Kentico Consultant didn't even find this in his report.

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on March 8, 2017 09:56

I'm glad you find the answer. That's what Kentico also recommends while fetching in ever way possible. Never access columns that you don't need.

For any page type that you are trying to access the data doesn't come from the corresponding table but from the CMS_View_Tree_Joined view. This view has >200 columns in it. SO although you think you're accessing it from the page type table however in reality it comes from the View. YOu could bookmark the link that I shared with you for any advance performance issues. Use it as a checklist.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.