Slow query: SELECT * FROM View_CMS_Tree_Joined WHERE (((NodeSiteID = ?) AND (Published = ?)) AND

Tom Barker asked on July 8, 2016 17:58

Hi,

I have a Kentico website running on a webfarm, all connected to a single database server.

Periodically (Completely irregularly) we're getting huge spikes in database activity, which is causeing the load balancer to respond "503: Service unavailable" as all the app servers are held up by the database.

We have New Relic APM running on the solution and we can see it's always the exact same query that is the problem:

SELECT * FROM View_CMS_Tree_Joined WHERE (((NodeSiteID = ?) AND (Published = ?)) AND ([DocumentCulture] = N?)) AND (NodeParentID = ?) ORDER BY NodeOrder, DocumentName

I'm quite new to Kentico (And the Microsoft stack in general) and I'm trying to work out what this query is be doing, why it occasionally slows down massively, and whats likely to be triggering it. On the face of it it doesn't look to troublesome, but I'm not sure if it's a sub query and New Relic just can't see that.

Any insight at all would be appreciated, possibly starting with whats "View_CMS_Tree_Joined" I can't seem to find a lot on it, and it doesn't appear to be a physical table?

Thanks, Jack

Correct Answer

Brenden Kehren answered on July 8, 2016 18:18

The query is calling a Kentico system view View_CMS_Tree_Joined. Depending on the number of pages you have in your site the query could bring the system to it's knees without a proper WHERE condition.

To better evaluate, it would be best to check out the site's webparts, transformations and custom code to see where this is being called and why. Most likely it's a webpart configured incorrectly. What version of Kentico are you on?

2 votesVote for this answer Unmark Correct answer

Recent Answers

Bryan Soltis answered on July 8, 2016 18:20

Hi Tom,

The View_CMS_Tree_Joined view is a system view combining several tables of the database. Doing a "SELECT *" against that view would pull back a tremendous amount of records and data, depending on the size of your site. I highly recommend looking into your web parts / data sources and specifying the specific columns you need. Most likely, you have a repeater or other web part that is pulling the data without filtering out any of the fields.

We also have a n open source tool called KInspector which can help you identify where you should specify specifc records. You find more about KInspector here:

https://github.com/Kentico/KInspector

Bryan

2 votesVote for this answer Mark as a Correct answer