CODEWITHSUNDEEP
asp.net-mvcasp.net-mvc-4
user1613714
user1613714

Reputation:

SimpleMembership Query Performance

I have created an ASP.NET MVC 4 Internet Application project. This project uses SimpleMembership. To register an account it runs 9 queries. It seems to be normal because this is standard code that came with the project, but isn't 9 queries a lot for just registering an account?

I also noticed that when I use:

if (Roles.IsUserInRole("..."))

it runs 4 queries...

Is this normal and can this become a performance issue?

Upvotes: 2

Views: 158

Answers (2)

Artless
Artless

Reputation: 4568

It's normal. But it could be a performance issue when your site grows.

Permissions is not something that changes much, if at all during a user's lifetime. The vast majority of your users will have the default permission set and only a (relatively) small number will have roles like "Admin" or "Moderator" or whatever. When your site becomes large, querying the Database for the permissions becomes quite expensive, especially since you're querying for the same data over and over again. Every time you render a page, you probably check for some permission. Does the user have permission to see the "Delete" button? Can he see some admin menu? If it's a forum or a blog post, can he see the poster's IP address? This sort of thing requires that you check for permissions multiple times on each page render, and you really don't want to do it via DB queries.

The proper solution would be to cache the permissions. Consider this:
When a user logs in, you query for all the permissions he has and cache it in the web server's memory. If you have multiple web servers, you'll need to either synchronize their caches, or have each individual server query for the user's permissions if that particular server doesn't have them in memory for that user. This would happen on demand when the user makes a request to that server.
Now you don't need to query the DB at all. All permission checks are done in memory, which will work much faster and leave your DB alone. If you change a user's permissions, you will need to sync all your web servers caches, of course, but since that is something that rarely occurs it would not have any performance impact.

You will want to clear the cache at some point, there's a limit to how much RAM your servers have. At this stage you could delete cached permissions for users who were last active a while ago and are probably not browsing your site anymore. Even if they are still there, their permissions will be reloaded in their next query.

Upvotes: 1

Amin Saqi
Amin Saqi

Reputation: 18977

Yeah, all are normal. In fact, user registration doesn't occur too much to be a concern for performance.

However, Roles.IsUserInRole("...") and other similar methods, can be called in many times, so be a performance issue.

If you have a web site with many concurrent users, maybe you should consider performing your own custom role checking queries.

Upvotes: 0

Related Questions