Large Query on page load

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.

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: Start with your master page.

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.

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.

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.

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.

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.

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

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

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.

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

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.

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:)

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.

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

Kentico Performance

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.

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.