Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

For my blog script I have a usergroup and permission system set up. Each user is assigned to a usergroup and I have a long list of permission settings. Both are stored in a MySQL database.

At the moment I perform around 10 permission queries for each page, for various content management functions. Coupled with another 20 or so queries for general blog use, the number of queries is building up.

Would it be more efficient to instead perform one query at the start of the script to retrieve all permission settings into an array, and then have all functions compare values in the array instead of checking the database?

share|improve this question
2  
Why is this not on Stack Overflow? –  configurator Jul 15 '11 at 9:02
5  
@configurator - because there's no code and it's a general design question rather than a specific coding question. Such questions are on topic. –  ChrisF Jul 15 '11 at 9:43
    
@ChrisF: Okay, I didn't know that. –  configurator Jul 15 '11 at 10:34
    
How often do these permission settings change for the average group? –  JeffO Jul 16 '11 at 1:51
    
My bad - originally posted at Stack OVERFLOW. Jeff, I want to be able to change the permission settings at any stage (but not in the current script, so permission settings can't be changed when a php script is run). –  Jared Jul 16 '11 at 11:48

2 Answers 2

Yes, 1 query is more efficient than X.

Have you considered further reducing queries by only doing this upon login and storing the permissions in session?

share|improve this answer
    
I have not considered it, no. It's a good idea, but I want to be able to change the permissions during the session by other users or the same user. –  Jared Jul 16 '11 at 11:50
    
@Jared then reload them whenever changes are made. Just make a function that reloads your permissions, and use it when necessary (ie login and permission changes) –  Chen Asraf Jul 16 '11 at 11:51
    
So whenever a permission setting is changed, a function is called to update the session with the new settings? It sounds a bit iffy, since I will be working with multiple sessions for multiple users. I haven't spent much time looking into sessions. –  Jared Jul 17 '11 at 9:28
    
If you must change permissions within a session, (which is debatable, would requiring a new login be a huge deal?), then I would suggest creating a hash of the permissions upon edit of permissions and then on each page checking the hash validity. –  Jonno Jul 19 '11 at 9:44

Building on what @Jonno said here: Is it more efficient to query once for all settings, or query whenever a setting is needed?

The original Q goes like this:

For my blog script I have a usergroup and permission system set up. Each user is assigned to a usergroup and I have a long list of permission settings. Both are stored in a MySQL database.

At the moment I perform around 10 permission queries for each page, for various content management functions. Coupled with another 20 or so queries for general blog use, the number of queries is building up.

I'm wonder if it's the more efficient choice to perform 1 query at the start of the script to add all permission settings to an array, then have all functions compare values in the array instead of checking the database.

Opinions?

How about facts and questions?

  • Fact: 1 query is less everything than x queries. (less time consumptive, less processing, etc)
  • Fact: Without profiling, you're just guessing. I don't program by guessing.
  • Question: Have you checked to see how intensive it is to do this?

I can answer that last one. It's "No, I haven't" and I know this because if you had, you would've come here with "how do I make this architecture faster". (One thing programmers seem to dislike is "but you didn't try it first!?!?!" so bear that in mind in the future.)

If you're doing it like most people, you're actually running the query through a whole other layer of stuff, that takes a long time (in cpu time) to actually return results and you want it to take less time. You could try several things, such as hand-coding the query, which would help, but would reduce the helpfulness of the intervening layers you're already using.

But what was suggested in Jonno's answer was the way I would suggest you to do it. Create an API for permissions. As you update a permission, you're doing it through this API, and the API both checks the current sessions for that user, updating when found, and then it updates the database. It also keeps track of any settings so changed, and undoes everything if an error is encountered. Yay transaction state. Granted, the naive way just tries to update and ignores any errors.

So we're gonna implement that in pseudocode, ok?

class sessionByUser{
  User user

  UpdateValue(key,value){
    foreach(session in Sessions){
      if (session.user == user)
        session.key = value
    }
    db.updateUserKey(key,value) //what you were doing before
  }
}

And you can see how immediately we do need to implement the rest of the crud ops, but this gives us the basics for a real-time updating of user session values.

Like I said, this is totally pseudocode, but this is how it's done, if you don't go back to the database every time. FWIW: The Microsoft ASP.NET user database goes back to the database every time to check if anything has updated. It has no noticeable load on my system.

Also, this setup works for other global values as well. Say you want to have a global object store for keeping track of things like API keys for other services, you could use this self-same concept of having a global singleton with accessor methods like this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.