When could database queries be reduced?

[NotionCommotion]

Let me give you a couple of examples.

 

For the first example, I have a table which includes all the pages I have on a website, and includes information about them such as some directory path, the minimum user access level to view, etc.  The only time it will change is if I add a page or modify settings on an existing one.  Instead of performing the query each time, maybe I should do it once for each user session and store the results in a session array?  But if I do, how do I force a reload should I ever make a change.  Or maybe I should do it once for all users, but this probably doesn't make much sense.

 

For a second example, I have a bunch of sites which use common code.  Each has their own subdomain which in turn identifies a primary key which with multiple joins makes each site unique.  The user could change the settings, however, will rarely do so.  Instead of querying the big query each time, should I store the settings in a session, and only query the database if some part of the session is not set or if the session "last_updated" value is different than that stored in the database? Yes, I will still need a query, but it will be small and hopefully more efficient).

 

For a third example, I have some JSON data available to the client.  Most clients will cache the data, so I shouldn't have much problems there.  I could also server cache it, but again, how do I know when to force a reload?

 

Thank you, and Happy New Year!

 

 

[Jacques1]

Before you start optimizing, you need to actually know what to optimize. You have it backwards: You randomly picked a bunch of techniques that you somehow liked, but it's entirely unclear if that's even your problem.

 

Optimization needs to be done systematically:

• Do you have a performance problem at all? If yes, how big is it? Are we talking about “I'd like to save a few microseconds” or “My clients have already complained”?
• Are you sure that the problem is caused by your application? Have you ruled out the hardware, the webserver and the network?
• If it's indeed your application, have you profiled it to find the actual bottleneck? 

When you know exactly where the problem is, then you can fix it. It's like debugging: When your application crashes, you need to find out the specific problem. You can't just pick a random script and shuffle a bunch of characters.

[NotionCommotion]

Before you start optimizing, you need to actually know what to optimize. You have it backwards: You randomly picked a bunch of techniques that you somehow liked, but it's entirely unclear if that's even your problem.

 

Optimization needs to be done systematically:

• Do you have a performance problem at all? If yes, how big is it? Are we talking about “I'd like to save a few microseconds” or “My clients have already complained”?
• Are you sure that the problem is caused by your application? Have you ruled out the hardware, the webserver and the network?
• If it's indeed your application, have you profiled it to find the actual bottleneck? 

When you know exactly where the problem is, then you can fix it. It's like debugging: When your application crashes, you need to find out the specific problem. You can't just pick a random script and shuffle a bunch of characters.

 

Thank you for your very real-world recommendations.  I agree with them and believe it is silly to worry about issues that are not issues.  I believe in the 80/20 rule, and if I don't know what 20% of my issues are causing 80% of my problems, I will have a hard time fixing anything.  That being said, are there times when we should attempt to identify bottlenecks before they become one in order to lessen the blow? I'm just looking for best practices in this topic so I am prepared.

[kicken]

That being said, are there times when we should attempt to identify bottlenecks before they become one in order to lessen the blow?

Generally you just avoid the obvious stuff, like don't run a bunch of queries in a loop, use a JOIN.

 

Focus on just writing code using good standards that is easy to manage and maintain first, don't worry about trying to optimize it. Once it works you can start profiling the code and start introducing optimizations later when and where they are necessary. Yes it means you might end up re-engineering something but at the same time you avoid over-engineering something else that never would have been a problem in the first place.

 

As for your original post's scenario with the mostly static menu, one thing that could be done is to store a pre-rendered menu into a memory cache such as Memcached or APC. Anywhere that you might do something which would affect the menu you would just wipe out the cache and let them all re-generate naturally on the next request.

[Jacques1]

Unfortunately, cache invalidation isn't quite that easy.

• If you permanently refresh the cache, the whole exercise becomes pointless; if you don't refresh it often enough, you risk missing an update.
• In a non-trivial application, the content may be changed in all kinds of places, which means you'll have a lot of trouble keeping the cache up-to-date.
• Sometimes there are out-of-order updates (e. g. manual changes in the database).

Caches can improve performance, but they can also be a big pain in the ass. So don't do it unless you have to.

[maxxd]

I agree with Jacques1 on caching. While in some very specific situations caching can be a good thing, most times it's an enormous source of headaches. Think through your application, make sure you're gathering only the data you need for the page, write sensible queries, and make sure your database is well tuned and you'll be able to get by without caching 9.5 out of 10 times. Plus, debugging is easier because you don't have to clear you cache with every freaking change you make to the code before you refresh your browser.