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?
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]
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.
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.
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.
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%
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
Adding the following index reduced the load time for the tree in the media section from 19 to about 2 seconds.
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:
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.datatypeNodeIdand
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.
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:
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.
is working on a reply...