Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Ole Martin Bakke 112 posts 624 karma points
    Feb 23, 2017 @ 09:19
    Ole Martin Bakke
    0

    Slow media section after upgrade 4.7.1 to 7.5.9

    Hi, I'm currently working on an upgrade from Umbraco 4.7.1 to version 7.5.9.

    On a refresh install of Umbraco 7.5.9 I have connected the old database and run the install/upgrade process. The upgrade is successfull and the backoffice run as expected with all the content in place.

    However the media-section is rather slow. It takes between 20 and 30 second to load the media tree, and if I expand a folder I takes about the same time to expand. In the original 4.7.1 solution the media three loads within a second, so something seems to have happened.

    Using the resource monitor in SQL Mangement Studio I can find there are two quires takeing a long time and one of the operations taking the longes time is ordering data from cmsPropertyData table by the id-column. In a query averaging on 19seconds on sort takes about 23% of the time and the other 32%, both is a sorting on this column.

    The following query is the one taking the longes time, with an average of 19seconds.

    SELECT * FROM (
    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 publishedVersion, latest.versionId as newestVersion, contenttype.alias, contenttype.icon, contenttype.thumbnail, contenttype.isContainer
    FROM umbracoNode umbracoNode
    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 umbracoNode parent
    ON parent.parentID = umbracoNode.id
    WHERE (umbracoNode.nodeObjectType = @0)
    AND (([umbracoNode].[parentID] = @1))
    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, contenttype.isContainer
    ) tmpTbl LEFT JOIN (
    SELECT contentNodeId, versionId, dataNvarchar, dataNtext, propertyEditorAlias, alias as propertyTypeAlias
    FROM [cmsPropertyData]
    INNER JOIN [umbracoNode]
    ON [cmsPropertyData].[contentNodeId] = [umbracoNode].[id]
    INNER JOIN [cmsPropertyType]
    ON [cmsPropertyType].[id] = [cmsPropertyData].[propertytypeid]
    INNER JOIN [cmsDataType]
    ON [cmsPropertyType].[dataTypeId] = [cmsDataType].[nodeId]
    WHERE (umbracoNode.nodeObjectType = @2)
    AND (([umbracoNode].[parentID] = @3))
    ) as property ON id = property.contentNodeId
    ORDER BY sortOrder, id
    

    When I look at the execution plan for the query this seems to be the part that takes most resources and time, just above 50%

    INNER JOIN [cmsPropertyType]
    ON [cmsPropertyType].[id] = [cmsPropertyData].[propertytypeid]
    

    I have also tried step upgrading to version 4.11.10 -> 6.0.5 -> 6.2.6, and all is good at version 6.0.5, but at version 6.2.6 the problem is present.

    How do I go forward to resolve these performance issues? Could this be an issue with missing, dupicated or to many indexes or are there other changes to the database who may cause this?

    Thanks, Ole Martin

  • Ole Martin Bakke 112 posts 624 karma points
    Feb 24, 2017 @ 14:49
    Ole Martin Bakke
    0

    Adding the following index reduced the load time for the tree in the media section from 19 to about 2 seconds.

    CREATE INDEX [IX_umbracoNode_parentID_nodeObjectType] ON [dbo].[umbracoNode] ([parentID], [nodeObjectType])

    But the load time for the content of the media section (the right side), and navigating in media picker remains slow, with load speed at between 7 and 10 seconds for a folder.

    EDIT 25.02.2017 After rebuilding indexes this index actully made the queries slow againg. You can try this, but you might have to remove it again running:

    DROP INDEX [IX_umbracoNode_parentID_nodeObjectType] ON [dbo].[umbracoNode]
    
  • Ole Martin Bakke 112 posts 624 karma points
    Feb 24, 2017 @ 15:12
    Ole Martin Bakke
    0

    There are now currently two queries averaging at about 4 to 4,5 seconds. There are

    SELECT cmsPropertyData.*
    FROM cmsPropertyData
    INNER JOIN cmsPropertyType
    ON cmsPropertyData.propertytypeid = cmsPropertyType.id
    INNER JOIN 
        (SELECT cmsContent.nodeId, cmsContentVersion.VersionId FROM [cmsContentVersion]
    INNER JOIN [cmsContent]
    ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
    INNER JOIN [umbracoNode]
    ON [cmsContent].[nodeId] = [umbracoNode].[id]
    WHERE (([umbracoNode].[nodeObjectType] = @0))
    AND (([umbracoNode].[parentID] = @1))
    ) as docData
    ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId
    LEFT OUTER JOIN cmsDataTypePreValues
    ON cmsPropertyType.dataTypeId = cmsDataTypePreValues.datatypeNodeId
    
    

    and

    select umbracoNode.id, umbracoNode.parentID, umbracoNode.sortOrder, cmsContentXml.xml, umbracoNode.level from umbracoNode
    inner join cmsContentXml on cmsContentXml.nodeId = umbracoNode.id and umbracoNode.nodeObjectType = @0
    where umbracoNode.id in (select cmsDocument.nodeId from cmsDocument where cmsDocument.published = 1)
    order by umbracoNode.level, umbracoNode.parentID, umbracoNode.sortOrder
    

    I have also tried Redgates SQL Compare and compared the database with a fresh 7.5.9 db and deployed the differences to the troubled database. That messed up a lot of id's, but the load time maintained at slow speed.

    I'm wondering if there could be some issues with the content in the database and not to db it self.

    The oldest content in the DB is problably from version 4.5. The umbracoNode-table contains about 5.400 rows, the cmsDocument about 112.000 and the cmsPropertyData about 2.200.000 rows.

  • Ole Martin Bakke 112 posts 624 karma points
    Feb 25, 2017 @ 13:47
    Ole Martin Bakke
    100

    It seems like I have now found the solution. I found a script to rebuild all the DB indexes, ran this, and so far so good. The tree load fast, and so does the media content (right side) and the media nodes in the media picker.

    I hope it continues like this.

    The script was this:

    USE DatabaseName --Enter the name of the database you want to reindex 
    
    
    
    DECLARE @TableName varchar(255) 
    
    
    
    DECLARE TableCursor CURSOR FOR 
    
    SELECT table_name FROM information_schema.tables 
    
    WHERE table_type = 'base table' 
    
    
    
    OPEN TableCursor 
    
    
    
    FETCH NEXT FROM TableCursor INTO @TableName 
    
    WHILE @@FETCH_STATUS = 0 
    
    BEGIN 
    
    DBCC DBREINDEX(@TableName,' ',90) 
    
    FETCH NEXT FROM TableCursor INTO @TableName 
    
    END 
    
    
    
    CLOSE TableCursor 
    
    
    
    DEALLOCATE TableCursor 
    

    I also removed the index from my second post, because after rebuilding indexes the media tree was back to loading slow, but after deliting that index, all was good.

Please Sign in or register to post replies

Write your reply to:

Draft