SI: Platform level slow loading response on HTML editors controls

Description

Issue has been discovered after upgrade from 7.2.2 to 7.3.2 in cloud staging instance & site have 10000 users , 5000 roles , 3000 page and around 1000 folders.
Total browser request time 27590MS .

Found the issue caused by GetServices stored procedure.
==============
/****** Object: StoredProcedure [dbo].[GetServices] Script Date: 9/9/2014 5:56:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetServices]
@PortalId Int, – pass Null for roles of all sites
@UserId Int – not null!
AS
BEGIN
SELECT
R.*,
UR.IsOwner,
UR.UserRoleID,
UR.UserID,
UR.ExpiryDate,
UR.IsTrialUsed,
UR.EffectiveDate,
U.DisplayName,
U.Email
FROM dbo.[Users] U
INNER JOIN dbo.[UserRoles] UR ON UR.UserID = U.UserID
RIGHT JOIN dbo.[Roles] R ON UR.RoleID = R.RoleID AND UR.UserID = @UserId
WHERE (R.PortalId = @PortalId OR IsNull(@PortalId, -1) = -1)
AND R.IsPublic = 1
AND R.RoleId >= 0 – DNN-4288: hide system role atm to prevent duplicates. Might be removed, after API has been adopted
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId UNKNOWN))
END
============

  • Got the better response time by using the follow ( AND U.UserID = @UserId ) *
    WHERE (R.PortalId = @PortalId OR IsNull(@PortalId, -1) = -1)
    AND R.IsPublic = 1
    AND R.RoleId >= 0 – DNN-4288: hide system role atm to prevent duplicates. Might be removed, after API has been adopted
    AND U.UserID = @UserId

SQL Trace file had been attached.

QA Test Plan

None

Activity

Show:
Bob Kruger
September 10, 2014, 11:22 PM

Per Cathal ===>
1. This is a badly written stored procedure and has been for since its creation – despite it’s clear intentions to return information for a user, it has never used the userid in the where clause i.e. it was performing badly in all releases. The user has likely only noticed now as their data has grown (though see point 4 for another possibility)
2. It appears that a community member's change to optimize it in 7.2.1 did cause it to perform slightly poorer, but really it was performing very badly already. His change removed some unnecessary duplicate entries, but the API was handling duplicates anyway I believe.
3. I’ve added the new clause and tested and it seems to be fine (from a data and performance perspective) – I’ve put the script below, you can run this via host->sql to test on the existing install. I’ve checked this into 7.3.3 already.
4. I do not see 4 uses of this in platform – the GetServices sproc is only called in the API if GetUserRoles is called and passes true as the 2nd parameter i.e. RoleController.Instance.GetUserRoles(_userInfo, true) . However in platform, there is only 1 usage of this (all the others pass “false”), and this is in generating the UserSocial object. As this is cached we only see 1 request in platform – as such I assume that either something in Evoq (or a 3rd party module) is calling this – you may wish to inspect the code to see if the call’s should really pass false (in which case they call “GetUserRoles” and not “GetServices”) –the main difference between “GetUserRoles” and “GetServices” is that “GetServices” does a right join to ensure that default/private roles such as “Subscribers” are included with the “GetServices” data.

The new clause follows...

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}GetServices]') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetServices]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetServices]
@PortalId Int, – pass Null for roles of all sites
@UserId Int – not null!
AS
BEGIN
SELECT
R.*,
UR.IsOwner,
UR.UserRoleID,
UR.UserID,
UR.ExpiryDate,
UR.IsTrialUsed,
UR.EffectiveDate,
U.DisplayName,
U.Email
FROM {databaseOwner}[{objectQualifier}Users] U
INNER JOIN {databaseOwner}[{objectQualifier}UserRoles] UR ON UR.UserID = U.UserID
RIGHT JOIN {databaseOwner}[{objectQualifier}Roles] R ON UR.RoleID = R.RoleID AND UR.UserID = @UserId
WHERE (R.PortalId = @PortalId OR IsNull(@PortalId, -1) = -1)
AND R.IsPublic = 1
AND R.RoleId >= 0
AND U.UserID = @UserId
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId UNKNOWN))
END
GO

Bob Kruger
September 10, 2014, 11:23 PM

Assigned back to Vikas for review and communication to the customer. Note that Cloud Team will also trial the changes in customer's staging site to ensure there is expected improvement and quality.

Bruce Chapman
September 12, 2014, 10:58 AM
Edited

Reposting in code block with JIRA syntax changes taken out

I have applied this to the ALS staging site where it was first found.

Ken Grierson
September 15, 2014, 6:14 PM

Per cathal's email:

This was tested and ran against the ALS site and everything appears fine (I also verified the data on a standard setup). If you like you can test this on a pre 7.3.3 by calling GetServices (passing in valid portalid and userid values), and then running the database change from https://github.com/dnnsoftware/Dnn.Platform/commit/623849ef2a3f1e82349c76701ac76f0c0758a5f7 via host->sql and rerunning it to verify that the data is correct

Per Vikas' Skype:

YEs did and it pass in local environment

Closed against 7.3.3

Assignee

Ken Grierson

Reporter

Vikas Sisodia

Story Size

Unknown

Severity

Major

Triage

New

Reported in Build #

None

Fixed in Build

Dev Owner

None

Includes Code Fix

No

Documentation Required

No

Trouble Ticket

08E-1BA0C9F9-031D

Requires More Info

None

QA Story Points

None

QA Owner

None

Injected

None

Automation Required

None

Code Review Owner

None

Components

Fix versions

Affects versions

Priority

High
Configure