On our newly lauched site we are using Umbraco 7.4.3. We installed NewRelic on the webserver to log performance, and for some reason these database queries are insanely slow (> 8 secs):
SELECT *
FROM [cmsContentVersion]
INNER JOIN [cmsContent]
ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
INNER JOIN [umbracoNode]
ON [cmsContent].[nodeId] = [umbracoNode].[id]
WHERE (([umbracoNode].[nodeObjectType] = @?))
AND (cmsContentVersion.VersionId = @?)
ORDER BY [cmsContentVersion].[VersionDate] DESC
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] = @?))
AND (cmsContentVersion.VersionId = @?)
) AS docData
ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId
LEFT OUTER JOIN cmsDataTypePreValues
ON cmsPropertyType.dataTypeId = cmsDataTypePreValues.datatypeNodeId
We are using uSitebuilder on the site, and its API to select children for a given page if that has anything to say.
Can you check the numbe of records in the cmsContentVersion and cmsPropertyData tables.
Every time you save a node umbraco creates a new version of content. Over time these tables can contain a lot of data causing slow queries.
We use a package called unVersion (https://our.umbraco.org/projects/website-utilities/unversion/) to clean up version automaticly.
Most of the time you won't need to keep the version going back to launch of the website.
Also I advise you strongly not to use uSitebuilder. This package is known to have a lot of performance issues on larger sites. If you need strongly typed models you can always use the Modelsbuilder that ships with Umbraco 7.4.x
cmsContentVersion contains 1000 records and cmsPropertyData contains 12000 records, but it is a fairly small site, so maybe thats a reasonable amount of data?
I am not sure how much would have to be re-written, by changing from usitebuilder to ModelsBuilder, but I'll dig into it.
Umbraco [7.4.3] db query is really slow
Hi
On our newly lauched site we are using Umbraco 7.4.3. We installed NewRelic on the webserver to log performance, and for some reason these database queries are insanely slow (> 8 secs):
SELECT * FROM [cmsContentVersion] INNER JOIN [cmsContent] ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId] INNER JOIN [umbracoNode] ON [cmsContent].[nodeId] = [umbracoNode].[id] WHERE (([umbracoNode].[nodeObjectType] = @?)) AND (cmsContentVersion.VersionId = @?) ORDER BY [cmsContentVersion].[VersionDate] DESC
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] = @?)) AND (cmsContentVersion.VersionId = @?) ) AS docData ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId LEFT OUTER JOIN cmsDataTypePreValues ON cmsPropertyType.dataTypeId = cmsDataTypePreValues.datatypeNodeId
We are using uSitebuilder on the site, and its API to select children for a given page if that has anything to say.
Any help would be much appreciated!
Hi Jesper,
uSiteBuilder is really slow package and it's not recommended to use it now.
Can you use miniprofiler for debugging which views taking the most sql queries? Than you will be able to fix the problem.
Thanks,
Alex
Hi Jesper,
Can you check the numbe of records in the cmsContentVersion and cmsPropertyData tables.
Every time you save a node umbraco creates a new version of content. Over time these tables can contain a lot of data causing slow queries.
We use a package called unVersion (https://our.umbraco.org/projects/website-utilities/unversion/) to clean up version automaticly.
Most of the time you won't need to keep the version going back to launch of the website.
Also I advise you strongly not to use uSitebuilder. This package is known to have a lot of performance issues on larger sites. If you need strongly typed models you can always use the Modelsbuilder that ships with Umbraco 7.4.x
Dave
Hi!
Thanks for the feedback!
I will give miniprofiler a try :)
cmsContentVersion contains 1000 records and cmsPropertyData contains 12000 records, but it is a fairly small site, so maybe thats a reasonable amount of data?
I am not sure how much would have to be re-written, by changing from usitebuilder to ModelsBuilder, but I'll dig into it.
Again - thanks for the replies!
best regards Jesper
Hi Jesper,
Judging the number of records the content doesn't seem to be the problem.
Miniprofiler is a good starting point for finding the performance issues.
By the way does this problem occur every time you view a page or only on the first view after a publish ?
Dave
is working on a reply...
This forum is in read-only mode while we transition to the new forum.
You can continue this topic on the new forum by tapping the "Continue discussion" link below.