Big performance hit with current RoleProvider

Apr 17, 2010 at 7:33 PM

We currently have about 15 roles.  When role provider is asked to give all roles for a given user (which asp.net does on each page access), it causes about TWENTY queries to database.  That compares with like 2 for native provider.

This is because the code currently queries for store, application, then list of roles, then access on each role.   We really need a method that returns the list of roles that a user has access to in one call.   Combine that with a page I have that has 50 links back into the website, where each link is a graphic generated by by custom code and you get a page that performs 10x slower with this provider than the one provided by default.

Additionally, I think it would be great to just use StorageCache in the role provider.  This way, it would be really quick to do any checking - could easily provide a method to invalidate the cache too. Andrea indicated that StorageCache is not thread safe - so, just need to find out why and correct that or do locking around it.

In any case - has anyone developed solutions to help resolve the above?

 

 

Apr 18, 2010 at 5:10 PM

I am sure this is probably not totally right, but here is a stored proc I made that returns the roles a given user has rights for.

This allows the roleprovider to make one database call instead of one for every role you define.  Big performance gain.  

Andrea, would this (with whatever additions/changes you need) be considered for inclusion into the main code base?

 

ALTER PROCEDURE [dbo].[netsqlazman_GetRolesForUser] (@TOKEN IMAGE, @VALIDFOR DATETIME, @LDAPPATH nvarchar(4000)) 
AS
--Memo: 0 - Role; 1 - Task; 2 - Operation
SET NOCOUNT ON

DECLARE @USERSID varbinary(85)
CREATE TABLE #USERGROUPS (objectSid VARBINARY(85))
-- Get User Sid
/*IF @USERGROUPSCOUNT>0
BEGIN
	SET @USERSID = SUBSTRING(@TOKEN,1,85)
	SET @I = CHARINDEX(0x01, @USERSID)
	SET @USERSID = SUBSTRING(@USERSID, @I, 85-@I+1)
	-- Get User Groups Sid
	SET @I = 0
	WHILE (@I<@USERGROUPSCOUNT)
	BEGIN
		SET @APP = SUBSTRING(@TOKEN,(@I+1)*85+1,85) --GET USER GROUP TOKEN PORTION
		SET @INDEX = CHARINDEX(0x01, @APP) -- FIND TOKEN START (0x01)
		SET @APP = SUBSTRING(@APP, @INDEX, 85-@INDEX+1) -- EXTRACT USER GROUP SID
		INSERT INTO #USERGROUPS (objectSid) VALUES (@APP)
		SET @I = @I + 1
	END
END
ELSE*/
BEGIN
	SET @USERSID = @TOKEN
END

-- PREPARE RESULTSET FOR BIZ RULE CHECKING
CREATE TABLE #PARTIAL_RESULTS_TABLE 
	(   
		[ItemId] [int] NOT NULL ,
		[ItemName] [nvarchar] (255)  NOT NULL ,
		[ItemType] [tinyint] NOT NULL,
		[AuthorizationType] TINYINT,
		[BizRuleId] [int] NULL ,
		[BizRuleSource] TEXT,
		[BizRuleLanguage] TINYINT
	)


DECLARE @ItemId INT, @RoleName VARCHAR(255)
DECLARE @AUTHORIZATION_TYPE TINYINT 
CREATE TABLE #ROLEMEMBERS (role VARCHAR(255))

DECLARE RolesWhereIAmAMember_cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
	SELECT ItemId, Name FROM dbo.netsqlazman_items() WHERE ApplicationId=5 AND ItemType=0 --role
OPEN RolesWhereIAmAMember_cur
FETCH NEXT FROM RolesWhereIAmAMember_cur INTO @ItemId, @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Recursive Call
	EXEC dbo.[netsqlazman_CheckAccess] @ItemId, @USERSID, @VALIDFOR, @LDAPPATH, @AUTHORIZATION_TYPE OUTPUT, 0, 0
	IF @AUTHORIZATION_TYPE=1 OR @AUTHORIZATION_TYPE=3 
		INSERT INTO #ROLEMEMBERS (role) VALUES (@RoleName)
	FETCH NEXT FROM RolesWhereIAmAMember_cur INTO @ItemId, @RoleName
END
CLOSE RolesWhereIAmAMember_cur
DEALLOCATE RolesWhereIAmAMember_cur

SELECT * FROM #ROLEMEMBERS

 

Apr 18, 2010 at 5:21 PM

Additionally, the storagecache needs to be thread safe.  Ideally, in ASP.NET we could create the storagecache object and store it in the application scope or more likely store it in the asp.net cache with an expiration.   This way each call into the website could use that object with no calls into the database.  Would be really great.   The role provider could be additionally configured (with an additional configuration parameter) to use/populate the storagecache object that everyone is sharing.  Would be a good ASP.NET solution.

I took a look at the storagecache and at first glance, it looks like if you simply removed the use of itemResultCache, then at least the code for checking access would appear to be thread safe.  The 'storeApplicationItemValidation' call initializes itemResultCache to a new hash collection each time and then internalCheckAccess uses that newly created hash collection - so we could just let 'internalCheckAccess' create the hash collection and use it locally?

I only looked at the basic 'CheckAccess' method as that is most of the calls, not sure what else may be necessary if using LDAP.

Thoughts?

Apr 19, 2010 at 12:53 AM

I now have a threadsafe storagecache implemented.

Additionally, I modified role provider to use the new storage cache.   Each request into a webpage now causes ZERO sql to run (after the first one).

Excellent....