U4-2329 - Browsing Media Slow in 6.1.1

Created by Brian Powell 05 Jun 2013, 15:48:01 Updated by Morten Christensen 12 Aug 2013, 16:06:30

In Umbraco 6.1.1, expanding the trees and browsing the Media section is slow. On the Content side, when I expand a tree I can see the elements in it basically instantaneously. In Media, it takes ~4 seconds each time I expand a new section of the tree before its elements are loaded and the right-side pane loads. This gets painful when you have to click through several levels on the tree.

I didn't notice the problem under 6.0.5 or earlier versions. I'm seeing it in 6.1.1 and I think it was also in 6.1.0. I'm running MySQL 5.6.11 FWIW.

Comments

Morten Christensen 07 Jun 2013, 07:29:12

I suspect this is related to MySQL. We tested 6.1.1 and the media tree against different sites (copies of big sites running live) that runs MSSQL, which is just as fast as the content section. Ironically the changes made in 6.1 was to improve performance.

One question though: Do you have any events that are touching the media tree in any way?

If I post the raw sql query could I get you to run it against your mysql db to verify whether its an issue there.


Brian Powell 07 Jun 2013, 13:50:14

I don't have any events that are touching the media tree. I'd be happy to run the raw SQL query against my database.


Brian Powell 27 Jun 2013, 14:50:00

@Morten, do you have that SQL? I'd like to test it out.


Morten Christensen 27 Jun 2013, 17:50:50

Yes, sorry. I got distracted by codegarden and forgot ;) I'll add the query later tonight or tomorrow.


Morten Christensen 28 Jun 2013, 11:59:22

Hi Brian,

Here is the query that is used for fetching media for the media tree (replace 1234 with the id of a media item):

SELECT umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, umbracoNode.nodeObjectType, umbracoNode.createDate, COUNT(parent.parentID) as children, published.versionId as publishedVerison, latest.versionId as newestVersion, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar as umbracoFile, property.controlId FROM umbracoNode umbracoNode LEFT JOIN umbracoNode parent ON parent.parentID = umbracoNode.id INNER JOIN cmsContent content ON content.nodeId = umbracoNode.id LEFT JOIN cmsContentType contenttype ON contenttype.nodeId = content.contentType LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE published = 1 GROUP BY nodeId, versionId) as published ON umbracoNode.id = published.nodeId LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE newest = 1 GROUP BY nodeId, versionId) as latest ON umbracoNode.id = latest.nodeId LEFT JOIN (SELECT contentNodeId, versionId, dataNvarchar, controlId FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN cmsDataType ON cmsPropertyType.dataTypeId = cmsDataType.nodeId) as property ON umbracoNode.id = property.contentNodeId WHERE umbracoNode.id = 1234 GROUP BY umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, umbracoNode.nodeObjectType, umbracoNode.createDate, published.versionId, latest.versionId, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar, property.controlId ORDER BY umbracoNode.sortOrder

You can also change the WHERE clause to be umbraco.parentID = 1234 in order to get a collection of media entries instead of a single one.

Let me know how this runs on MySql.

Thanks!


Brian Powell 28 Jun 2013, 16:02:56

Running the query as-is on my 6.1.1 installation (upgraded from ~4.7.2 to various versions along the way), took 1.9 seconds.

Going through the query, I noticed a lot of joins were happening on non-indexed fields. I added some additional indexes which cut the running time to about 0.8 seconds:

  • added VERSIONID field to existing cmsDocument.NODEID index
  • created index on cmsdatatype.nodeID
  • created index on cmspropertytype.datatypeid
  • created index on cmscontent.contenttype
  • created index on cmsdocument.published
  • created index on cmsdocument.newest

I haven't been able to figure out why, perhaps an optimization bug in MySQL, but that last LEFT JOIN really bloats things. When I use EXPLAIN to see what MySQL is doing, it seems to be claiming it is using an index to join the subquery with umbracoNode but from the performance it sure doesn't seem like it. Without that last LEFT JOIN, the query runs in about 0.02 seconds.

If I can restrict the results coming out of that last subquery, performance is good - about 0.02 seconds. Changing the query to something like this works well: SELECT umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, umbracoNode.nodeObjectType, umbracoNode.createDate, COUNT(parent.parentID) as children, published.versionId as publishedVerison, latest.versionId as newestVersion, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar as umbracoFile, property.controlId FROM umbracoNode umbracoNode LEFT JOIN umbracoNode parent ON parent.parentID = umbracoNode.id INNER JOIN cmsContent content ON content.nodeId = umbracoNode.id LEFT JOIN cmsContentType contenttype ON contenttype.nodeId = content.contentType LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE published = 1 GROUP BY nodeId, versionId) as published ON umbracoNode.id = published.nodeId LEFT JOIN (SELECT nodeId, versionId FROM cmsDocument WHERE newest = 1 GROUP BY nodeId, versionId) as latest ON umbracoNode.id = latest.nodeId LEFT JOIN (SELECT contentNodeId, versionId, dataNvarchar, controlId FROM cmsPropertyData INNER JOIN umbracoNode ON umbracoNode.ID = cmsPropertyData.contentNodeID INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN cmsDataType ON cmsPropertyType.dataTypeId = cmsDataType.nodeId WHERE umbracoNode.ID = 5014 ) as property ON umbracoNode.id = property.contentNodeId WHERE umbracoNode.id = 5014 GROUP BY umbracoNode.id, umbracoNode.trashed, umbracoNode.parentID, umbracoNode.nodeUser, umbracoNode.level, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.uniqueID, umbracoNode.text, umbracoNode.nodeObjectType, umbracoNode.createDate, published.versionId, latest.versionId, contenttype.alias, contenttype.icon, contenttype.thumbnail, property.dataNvarchar, property.controlId ORDER BY umbracoNode.sortOrder

I know it's a bit kludgy but it seems to work. Any thoughts?


Morten Christensen 01 Jul 2013, 08:04:12

@Brian thanks for testing this! I think it makes a lot of sense to add a where clause to the query in the last LEFT JOIN. I thought this bit "ON umbracoNode.id = property.contentNodeId" would be enough to limit the results, but from your tests it sounds like MySql handles the inner selects a bit differently.

I'll look into adding this for 6.1.3


Sebastiaan Janssen 22 Jul 2013, 09:24:10

As Morten is on holiday, this won't get fixed for 6.1.3, delayed until next version. :)


Morten Christensen 12 Aug 2013, 12:05:35

@Brian I have made some corrections to the query as discussed above and its part of the latest nightly build: http://nightly.umbraco.org/umbraco%206.1.4/UmbracoCms.6.1.4-build.32.zip I'd appreciate if you had time to try it out. Let me know if you have, thanks.


Brian Powell 12 Aug 2013, 14:30:38

@Morten, thanks for your work with this fix. Things are still a little sluggish but it's a big improvement over before.


Morten Christensen 12 Aug 2013, 16:06:30

Okay, not sure what else to do. I can double check the other subqueries but just seems a little unusual to me that MySql would react so differently to those (fairly common) queries as apposed to SQL CE and MS SQL.


Priority: Normal

Type: Bug

State: Fixed

Assignee: Morten Christensen

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.1.1

Due in version: 6.1.4

Sprint:

Story Points:

Cycle: