userPermissionCache is being built too slow

Jun 4, 2009 at 2:06 PM

Hi andrea,

I was planning to use userPermissionCache for web application authorization. And I thought it wouldnt take too much time to have it built, because I will say who the user is and it would use that info to extract authorization records within sql stuff related to the process. But it seems it fetchs all the items of the application ..

Actually what I want to ask generally is, how many items, do you think, can I have for an sqlAzmanApplication at most ? Now I am having some performance tests, I put 3,5 millons of items for an application.. because of the caching processes, it looks I would be in trouble.

First I commented out a part in sqlazmanApplicaition.GetItems() method, which fetch data from itemsHierarchyTable and use that data to load sqlAzmanItem.Items and ItemsWhereIamAMember properties. these properties remain null when I do this. And luckily when they are null, it works in a loadOnDemand way.

Now I have to find a way to fetch userPermissionCache' datas quicker or I have to find another way to authorize my document's (Which may be millions of) if I am not fired yet  : )


Jun 4, 2009 at 2:16 PM
Hi tusanesen,
have you tried the WCF Cache Service (based on StorageCache class) ?
The StorageCache time to builb is less then UserPermissionCache.

Jun 4, 2009 at 4:44 PM


I guess storagecache holds everything, items from all applications.. but I need to seperate applications. Because one application has millions of items (for documents), other one has only a few thousands. I need to cache smaller one in IIS side.  Because items from different applications are stored in the same table, and buildUserPermissionCache sp is using dbo.GetItems() function without applicationId paramater, it needs to fetch all items instead of the items from the application that I have given SP as  paramater. I think this must be fixed.



Jun 4, 2009 at 5:00 PM

For the wcf cache service suggestion, I cant use wcf cache service for User interface authorization as I never want to get involved with wcf channels for every checkAccess  so I need to cache something in IIS side. I need that caching to be in applicaiton level if not user+application. ? Otherwise I will need different storages. then would have other problems such as defiining same users and groups in different places.



Jun 4, 2009 at 6:17 PM


It Is not exact.

The StorageCache class can even store data for a single NetSqlAzMan Application.

Indeed, the method BuildStorageCache() has an overload that accepts two parameters: storeName, ApplicationName.

The same is for the WCF Service Cache.  (storeNameFilter, applicationNameFilter in the web.config file).

If you do not want to use the WCF service will still use the class StorageCache (instead of the class UserPermissionCache) since its construction is much faster and mostly reads from DB data indispensable.


Andrea Ferendeles
NetSqlAzMan - Project Coordinator

Jun 4, 2009 at 6:19 PM

As I said in previous post, you may also want to use the class StorageCache (filtering only 1 Application) and keep it in session.

Andrea Ferendeles
NetSqlAzMan - Project Coordinator

Jun 5, 2009 at 7:44 AM

thanks for your quick help..

I checked StorageCache.BuildStorageCache(string storeNameFilter, string applicationNameFilter) method now. It takes applicationName parameter, but it fetchs all the items in the storage. So Its still slow.

Even more because I have too many items in one of the applications (or if I had too many applications with small number of items, same problem would appear), I run into out of memory exception with my 2 gb ram. Application Server would have much more RAM for sure.. But the problem is why I have to fetch all the items when I give applicationName parameter ?

Jun 5, 2009 at 8:46 AM


you have right.

I will fix it for the next coming release.

In this way … if you call BuildStorageCache with StoreNameFilter/ApplicationNameFilter … only affected records will be collected.



Jun 5, 2009 at 8:47 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jun 5, 2009 at 4:39 PM

hi again..

I told abt this before, but couldnt be sure if I could explain well.  SqlAzmanApplication.Items property retrieves all the items when its null. And I couldnt find a way to get rid of this behaviour. When I have too many items I dont want to load them into the memory. Just writing and reading to/from db is enough for such an application. So I think there must be an option to disable this caching mechanism.

as I mentioned above in the disgussion, sqlAzmanApplication.GetItems() method retrieve all the items and load child and parent items in another iteration and goes to db for each iteration. Especially this process is too slow and I commented out this part. In this case sqlAzmanItem.Members, itemsWhereIAmAMember properties remains null but when they are called, child and parent items are retrieved for that item only. So this works well. This may also be considered to be optional rather than being commented out by me : )

And other part  that retrieve all the items are still too slow and holding too much memory  when items are crowded. So I need an option to totally disable this caching mechanism.


regards ..

Jun 6, 2009 at 10:26 AM

Hi tusanesen,

I have discovered that LINQ deferred execution was the reason for which there was a select for each Item.

I have disabled it by adding a ToList() to each LINQ query on Stores, Applications, Items, Authorizations and Attributes.

The result is that now a single query retrieve all Items (ToList() force 1 execution).

Please, when I will publish the new release, check again your application performance.

Kind regards,


Jun 8, 2009 at 8:28 AM release published.
Please try and let me know.


Jun 8, 2009 at 4:19 PM

hi andrea,

I got your new release.. thanks.  I noticed you got buildStorageCache() faster by putting applicationName and storeName filters. But pls note that, using applicationID and storeId will be much faster in those queries.  I cought sql statements (AllItemsFQ) with the profiler and tried both filtering applicationName, storeName and applicationId, StoreId .. those with ID is really much faster. you should consider getting ID's in seperate queries instead of in these join statements.

exec sp_executesql N'SELECT [t0].[ItemId], [t0].[ApplicationId], [t0].[Name], [t0].[Description],
[t0].[ItemType], [t0].[BizRuleId]
FROM [dbo].[Items]() AS [t0]
INNER JOIN ([dbo].[Stores]() AS [t1]
    INNER JOIN [dbo].[Applications]() AS [t2] ON [t1].[StoreId] = [t2].[StoreId]) ON [t0].[ApplicationId] =
WHERE ([t2].[Name] LIKE @p0 ESCAPE ''~'') AND ([t1].[Name] LIKE @p1)
ORDER BY [t0].[Name], [t2].[Name]',N'@p0 nvarchar(21),@p1

Jun 8, 2009 at 4:43 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jun 8, 2009 at 4:44 PM

Excellent contribution.
I will do it for the next release.