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
Activity
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
Reposting in code block with JIRA syntax changes taken out
I have applied this to the ALS staging site where it was first found.
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.
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