Jump to content

When could database queries be reduced?


NotionCommotion

Recommended Posts

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!

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.