SI: Platform level slow loading response on HTML editors controls

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

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.

Reposting in code block with JIRA syntax changes taken out

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

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