duplicate sql queries shown in debug trace

Martin 114 posts 313 karma points

Sep 27, 2015 @ 11:06

Hi,

When I look at the dubug trace, I often see warnings about duplicate sql queries. At most I have had 27 duplicate queries for the same process. And I have no idea why I have it? I am running Umbraco 7.2.5.

Anybody experienced the same? And perhaps have solutions? It also seems that the app pool shuts down every now and then without any legitimate reason (although I have add a scheduled task every hour to prevent my web hosting provider to shut down the app pool due to idle time).

Regards, Martin

Copy Link

Artyom Chernenko 17 posts 91 karma points

Sep 28, 2015 @ 04:24

Its hard to say anything definite without any examples, so the general advise is - try to use caching for the case with duplicate SQL queries.

Also review your code and make sure that you are not using Services (MemberService, ContentService, etc) for selecting data - they generate SQL queries for the data retrieving. You should use UmbracoHelper for that purpose, it uses internal caches and doesnt interact with the DB.

Copy Link

Martin 114 posts 313 karma points

Sep 29, 2015 @ 16:42

Hej Artyom,,

Some example of duplicate sql queries are:

704.00 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 651.00 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+1055.0 ms Reader 52.1 ms

DECLARE @0 nvarchar(40) = N'a2cb7800-f571-4787-9638-bc48539a0efb', @1 nvarchar(4000) = N'Hem';

SELECT * FROM [cmsContentType] INNER JOIN [umbracoNode] ON [cmsContentType].[nodeId] = [umbracoNode].[id] LEFT JOIN [cmsDocumentType] ON [cmsDocumentType].[contentTypeNodeId] = [cmsContentType].[nodeId] WHERE ([umbracoNode].[nodeObjectType] = @0) AND ([cmsContentType].[alias] = @1) ORDER BY ([umbracoNode].[text])

103.70 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 103.70 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+1210.8 ms Reader 78.4 ms

MoveNext MapContentTypes GetContentTypes PerformGetAll GetAll PerformGetByQuery GetByQuery GetContentType CreatePublishedContentType

DECLARE @0 nvarchar(40) = N'a2cb7800-f571-4787-9638-bc48539a0efb', @1 int = 1063;

SELECT cmsDocumentType.IsDefault as dtIsDefault, cmsDocumentType.templateNodeId as dtTemplateId, cmsContentType.pk as ctPk, cmsContentType.alias as ctAlias, cmsContentType.allowAtRoot as ctAllowAtRoot, cmsContentType.description as ctDesc, cmsContentType.icon as ctIcon, cmsContentType.isContainer as ctIsContainer, cmsContentType.nodeId as ctId, cmsContentType.thumbnail as ctThumb, AllowedTypes.allowedId as ctaAllowedId, AllowedTypes.SortOrder as ctaSortOrder, AllowedTypes.alias as ctaAlias,
ParentTypes.parentContentTypeId as chtParentId, umbracoNode.createDate as nCreateDate, umbracoNode.[level] as nLevel, umbracoNode.nodeObjectType as nObjectType, umbracoNode.nodeUser as nUser, umbracoNode.parentID as nParentId, umbracoNode.[path] as nPath, umbracoNode.sortOrder as nSortOrder, umbracoNode.[text] as nName, umbracoNode.trashed as nTrashed, umbracoNode.uniqueID as nUniqueId,
Template.alias as tAlias, Template.nodeId as tId, Template.text as tText FROM cmsContentType INNER JOIN umbracoNode ON cmsContentType.nodeId = umbracoNode.id LEFT JOIN cmsDocumentType ON cmsDocumentType.contentTypeNodeId = cmsContentType.nodeId LEFT JOIN ( SELECT cmsContentTypeAllowedContentType.Id, cmsContentTypeAllowedContentType.AllowedId, cmsContentType.alias, cmsContentTypeAllowedContentType.SortOrder FROM cmsContentTypeAllowedContentType
INNER JOIN cmsContentType ON cmsContentTypeAllowedContentType.AllowedId = cmsContentType.nodeId ) AllowedTypes ON AllowedTypes.Id = cmsContentType.nodeId LEFT JOIN ( SELECT * FROM cmsTemplate INNER JOIN umbracoNode ON cmsTemplate.nodeId = umbracoNode.id ) as Template ON Template.nodeId = cmsDocumentType.templateNodeId LEFT JOIN cmsContentType2ContentType as ParentTypes ON ParentTypes.childContentTypeId = cmsContentType.nodeId
WHERE (umbracoNode.nodeObjectType = @0) AND (umbracoNode.id IN (@1))

1074.70 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 1074.70 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+2363.9 ms Reader 29.1 ms

DECLARE @0 nvarchar(40) = N'6fbde604-4178-42ce-a10b-8a2600a2f07d', @1 int = 1090;

SELECT * FROM [cmsTemplate] INNER JOIN [umbracoNode] ON [cmsTemplate].[nodeId] = [umbracoNode].[id] WHERE ([umbracoNode].[nodeObjectType] = @0) AND (umbracoNode.id in (@1))

29.40 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 29.40 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+2422.4 ms DUPLICATE Reader 2.9 ms

DECLARE @0 nvarchar(40) = N'6fbde604-4178-42ce-a10b-8a2600a2f07d', @1 int = 1054;

SELECT * FROM [cmsTemplate] INNER JOIN [umbracoNode] ON [cmsTemplate].[nodeId] = [umbracoNode].[id] WHERE ([umbracoNode].[nodeObjectType] = @0) AND (umbracoNode.id in (@1))

116.70 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 116.70 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+2542.0 ms Reader 42.2 ms

MoveNext MapGroupsAndProperties MapContentTypeChildren GetContentTypes PerformGetAll GetAll PerformGetByQuery GetByQuery GetContentType CreatePublishedContentType

DECLARE @0 int = 1063, @1 int = 1063;

SELECT PG.contenttypeNodeId as contentTypeId, PT.ptId, PT.ptAlias, PT.ptDesc, PT.ptHelpText, PT.ptMandatory, PT.ptName, PT.ptSortOrder, PT.ptRegExp, PT.dtId, PT.dtDbType, PT.dtPropEdAlias, PG.id as pgId, PG.parentGroupId as pgParentGroupId, PG.sortorder as pgSortOrder, PG.[text] as pgText FROM cmsPropertyTypeGroup as PG LEFT JOIN ( SELECT PT.id as ptId, PT.Alias as ptAlias, PT.[Description] as ptDesc, PT.helpText as ptHelpText, PT.mandatory as ptMandatory, PT.Name as ptName, PT.sortOrder as ptSortOrder, PT.validationRegExp as ptRegExp, PT.propertyTypeGroupId as ptGroupId, DT.dbType as dtDbType, DT.nodeId as dtId, DT.propertyEditorAlias as dtPropEdAlias FROM cmsPropertyType as PT INNER JOIN cmsDataType as DT ON PT.dataTypeId = DT.nodeId ) as PT ON PT.ptGroupId = PG.id WHERE (PG.contenttypeNodeId in (@0))

                    UNION

                    SELECT  PT.contentTypeId as contentTypeId,
                            PT.id as ptId, PT.Alias as ptAlias, PT.[Description] as ptDesc, PT.helpText as ptHelpText,
                            PT.mandatory as ptMandatory, PT.Name as ptName, PT.sortOrder as ptSortOrder, PT.validationRegExp as ptRegExp,
                            DT.nodeId as dtId, DT.dbType as dtDbType, DT.propertyEditorAlias as dtPropEdAlias,
                            PG.id as pgId, PG.parentGroupId as pgParentGroupId, PG.sortorder as pgSortOrder, PG.[text] as pgText
                    FROM cmsPropertyType as PT
                    INNER JOIN cmsDataType as DT
                    ON PT.dataTypeId = DT.nodeId
                    LEFT JOIN cmsPropertyTypeGroup as PG
                    ON PG.id = PT.propertyTypeGroupId WHERE (PT.contentTypeId in (@1))

ORDER BY (pgId)

238.50 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 238.50 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+2822.7 ms DUPLICATE Reader 8.3 ms

MoveNext MapContentTypes GetContentTypes PerformGetAll GetAll MapContentTypeChildren GetContentTypes PerformGetAll GetAll PerformGetByQuery GetByQuery GetContentType CreatePublishedContentType

DECLARE @0 nvarchar(40) = N'a2cb7800-f571-4787-9638-bc48539a0efb', @1 int = 1055;

SELECT cmsDocumentType.IsDefault as dtIsDefault, cmsDocumentType.templateNodeId as dtTemplateId, cmsContentType.pk as ctPk, cmsContentType.alias as ctAlias, cmsContentType.allowAtRoot as ctAllowAtRoot, cmsContentType.description as ctDesc, cmsContentType.icon as ctIcon, cmsContentType.isContainer as ctIsContainer, cmsContentType.nodeId as ctId, cmsContentType.thumbnail as ctThumb, AllowedTypes.allowedId as ctaAllowedId, AllowedTypes.SortOrder as ctaSortOrder, AllowedTypes.alias as ctaAlias,
ParentTypes.parentContentTypeId as chtParentId, umbracoNode.createDate as nCreateDate, umbracoNode.[level] as nLevel, umbracoNode.nodeObjectType as nObjectType, umbracoNode.nodeUser as nUser, umbracoNode.parentID as nParentId, umbracoNode.[path] as nPath, umbracoNode.sortOrder as nSortOrder, umbracoNode.[text] as nName, umbracoNode.trashed as nTrashed, umbracoNode.uniqueID as nUniqueId,
Template.alias as tAlias, Template.nodeId as tId, Template.text as tText FROM cmsContentType INNER JOIN umbracoNode ON cmsContentType.nodeId = umbracoNode.id LEFT JOIN cmsDocumentType ON cmsDocumentType.contentTypeNodeId = cmsContentType.nodeId LEFT JOIN ( SELECT cmsContentTypeAllowedContentType.Id, cmsContentTypeAllowedContentType.AllowedId, cmsContentType.alias, cmsContentTypeAllowedContentType.SortOrder FROM cmsContentTypeAllowedContentType
INNER JOIN cmsContentType ON cmsContentTypeAllowedContentType.AllowedId = cmsContentType.nodeId ) AllowedTypes ON AllowedTypes.Id = cmsContentType.nodeId LEFT JOIN ( SELECT * FROM cmsTemplate INNER JOIN umbracoNode ON cmsTemplate.nodeId = umbracoNode.id ) as Template ON Template.nodeId = cmsDocumentType.templateNodeId LEFT JOIN cmsContentType2ContentType as ParentTypes ON ParentTypes.childContentTypeId = cmsContentType.nodeId
WHERE (umbracoNode.nodeObjectType = @0) AND (umbracoNode.id IN (@1))

80.90 ms
[PublishedContentRequestEngine] FindPublishedContent: Begin finders — 80.90 ms [PublishedContentRequestEngine] FindPublishedContent: Begin finders T+2911.9 ms DUPLICATE Reader 6.2 ms

MoveNext MapGroupsAndProperties MapContentTypeChildren GetContentTypes PerformGetAll GetAll MapContentTypeChildren GetContentTypes PerformGetAll GetAll PerformGetByQuery GetByQuery GetContentType CreatePublishedContentType

DECLARE @0 int = 1055, @1 int = 1055;

SELECT PG.contenttypeNodeId as contentTypeId, PT.ptId, PT.ptAlias, PT.ptDesc, PT.ptHelpText, PT.ptMandatory, PT.ptName, PT.ptSortOrder, PT.ptRegExp, PT.dtId, PT.dtDbType, PT.dtPropEdAlias, PG.id as pgId, PG.parentGroupId as pgParentGroupId, PG.sortorder as pgSortOrder, PG.[text] as pgText FROM cmsPropertyTypeGroup as PG LEFT JOIN ( SELECT PT.id as ptId, PT.Alias as ptAlias, PT.[Description] as ptDesc, PT.helpText as ptHelpText, PT.mandatory as ptMandatory, PT.Name as ptName, PT.sortOrder as ptSortOrder, PT.validationRegExp as ptRegExp, PT.propertyTypeGroupId as ptGroupId, DT.dbType as dtDbType, DT.nodeId as dtId, DT.propertyEditorAlias as dtPropEdAlias FROM cmsPropertyType as PT INNER JOIN cmsDataType as DT ON PT.dataTypeId = DT.nodeId ) as PT ON PT.ptGroupId = PG.id WHERE (PG.contenttypeNodeId in (@0))

                    UNION

                    SELECT  PT.contentTypeId as contentTypeId,
                            PT.id as ptId, PT.Alias as ptAlias, PT.[Description] as ptDesc, PT.helpText as ptHelpText,
                            PT.mandatory as ptMandatory, PT.Name as ptName, PT.sortOrder as ptSortOrder, PT.validationRegExp as ptRegExp,
                            DT.nodeId as dtId, DT.dbType as dtDbType, DT.propertyEditorAlias as dtPropEdAlias,
                            PG.id as pgId, PG.parentGroupId as pgParentGroupId, PG.sortorder as pgSortOrder, PG.[text] as pgText
                    FROM cmsPropertyType as PT
                    INNER JOIN cmsDataType as DT
                    ON PT.dataTypeId = DT.nodeId
                    LEFT JOIN cmsPropertyTypeGroup as PG
                    ON PG.id = PT.propertyTypeGroupId WHERE (PT.contentTypeId in (@1))

ORDER BY (pgId)

4772.60 ms
RazorView.Render: ~/Views/Partials/Slideshow.cshtml — 1161.10 ms

I dont know if it "normal" to have some DUBLICATE readers for Umbraco but I think I have a lot. Thank for any advice /martin

Copy Link

is working on a reply...

Flag this post as spam?

Duplicate sql queries shown in debug trace