U4-2123 - Umbraco Version 6.0.3 Serious Performance Problems when switching between Content and the Recycle Bin when a lot of media has been uploaded

Created by John Blair 17 Apr 2013, 16:53:11 Updated by Sebastiaan Janssen 06 May 2013, 12:56:36

Duplicates: U4-1604

Relates to: U4-2176

We are experiencing delays of between 10 and 15 seconds just selecting the Content folder or the Recycle Bin in the Content section. We notice the delay increases the more media we upload.

On investigation, using SQL Profiler we notice that a query is run to do a version check for each media node. This did NOT happen in version 6.0.0 Is this a deliberate change or a bug that media nodes are leaking into building the content tree?

We use a lot of media - approx 6000 items and this is having a massive performance impact on us.

Could theses checks on media nodes be removed if not needed when the user is just browsing the content section?

2 Attachments

Download sqltraceofContentclick.xml

Download annotated umbraco sql trace.xlsx

Comments

Morten Christensen 17 Apr 2013, 20:35:01

Can you elaborate on the version check? I don't recall any specific version checks on media, especially because media is not versioned the same way as content.


John Blair 18 Apr 2013, 08:56:27

Thanks for the reply Morten

We seeded a new 6.0.3 database with Document Types, Scripts, Templates, Macros - but no content pages - using a package install. We found clicking in the Content section between the "Content" and "Recycle" icons performance was fine - no content displayed in either as expected.

When we SQL profiled the database we found a few dozen of the SQL queries (listed below) were being generated by the clicking action of clicking on the Content folder and the Recycle bin in the content folder.

However, once we uploaded all the Media and went back to the "Content" section and repeated the clicking action we noticed the performance was very slow - about 10 to 15 seconds to respond to the clicks (as opposed to sub second).

The number of SQL statements being executed has exploded from a few dozen to 3502 - this indicated the presence of the media is having an effect on the display of the Content tree section (which is still empty as expected).

After uploading all the media, clicking on the Content node produced the following:

3502 Rows:

First one: Select versionId from cmsContentVersion where contentID = 4487 order by id desc Last one: Select versionId from cmsContentVersion where contentID = 1088 order by id desc

All the queries are the same except for the contentID A follow up click on the recycle bin in the Content section produced the same set of queries.

We are surprised that uploading media has an effect on the display of the content area. We were also wondering why all the queries were not done in a single SQL statement?

All the media is displaying fine (3117 images and the remainder media folders). Each time we add a new media image, this increases the number of queries being run when we select the Content section, Content icon or Recycle bin icon.

We have a 14Mb db we could e-mail you or put on a drop point if that would help? I have attached the SQL trace file. Let me know if you need any more info. Thanks.


John Blair 18 Apr 2013, 09:34:31

We have attached a reproduceable scenario for you from a blank installation of an umbraco 6.0.3 database. See the comments in the attached file for the steps to reproduce. Hopefully you will see that an extra query is run in the Content section for each media item uploaded.


Sebastiaan Janssen 18 Apr 2013, 12:10:25

Do you happen to have the last edits dashboard item enabled currently? If so, try and disable it (remove from Dashboard.config) and see if that helps.


John Blair 18 Apr 2013, 12:31:10

Thanks Sebastiaan,

Excellent suggestion. Yes, that fixed the performance problem - performance is back to normal.

Hopefully, this issue will still be addressed by a code fix.

Suggestion to the umbraco team: In researching this problem we notice a lot of SQL is embedded in the .net source code rather than making use of stored procedures - would help performance if all queries were moved to the database. We also noticed a lot of queries for say single property data when it would be more efficient to make a single query for say all the property data of a document type etc. Likewise, the 3000+ queries we saw for the media version info - could all be done in a single query which should massively improve performance.

Thanks a lot for your help.


Robert Bullock 02 May 2013, 15:13:59

I am having a similar performance issue... expanding any node in Content takes 10-15 seconds. I'm not sure what is causing the bottleneck... I don't have SQL Profiler on the server.


John Blair 02 May 2013, 16:17:52

Per Sebastiaan's suggestion - have you removed the "Last Edits" tab from the Content section by editing the Dashboard.config file - the control on the Last Edits tab was responsible for issuing a lot of SQL queries. Per my previous comment - that fixed it for me. I also notice that the control was modified and this issue is reported as fixed in 6.0.4.


Priority: Normal

Type: Bug

State: Duplicate

Assignee: Morten Christensen

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 4.11.6, 6.0.3

Due in version:

Sprint:

Story Points:

Cycle: