Welcome Guest, Not a member yet? Register   Sign In
CMS / System Resource question?

I have built a CMS/E-Commerce application and im running through some profile checks and noticing up to 20 query calls per page.

**Note: Menus, Site Configuration, page content, session are all calls to the db. Only 1-2 db calls are made for content depending on what page is called most of the time i've limited it down to 1 db call.

What would be a typical amount of query calls and what type of system memory should be ideal? Obviously as low as possible, but would would be the ideal range to shoot for?

The number of queries isn't really the best way to focus on optimization with a framework in my opinion. I would turn on slow query logs and analyze those instead. Look at things like system load, caching, etc...

Ok, so the amount of queries isnt that big of concern as long as they are not huge resource hogs? Each of these queries run time is only about .0005 seconds at most?

I agree, the number of calls by itself is not the best measurement. But they can be the source of problemens when the load on the site gets bigger. Once calls have to wait for each other to finish, you will see performance degredation. Of course, looking at slow queries will tell you when that happens.

If you want to optimize you can try to reduce the number of calls by writing smarter queries if possible, but also by caching. For instance, if menus or configurations only rarely change, you could store them in a file and only update that file when the information actually changes.

I've thought about putting the info into a file but I have other people that need to be able to change these variables and I need their access of changes to be limited and structured as they are not developers or coders. These changes are made on live e-commerce sites that generate 20-30k a day and if they go down without knowing it could mean a loss of 20-30k which isn't an option.

I've also considered db caching? but haven't looked into it too much yet.

Will consider this as your right the menu and configurations don't change often but do change from time to time. All my tables are well indexed and I have pretty much cut down any request with joins and added pivot tables which connect tables smartly but... still looks like the session class writes to the table constantly to update the session etc. which brings up another question about the efficiency of the session class.

Awww... the joy of developing Wink

Take a look at Phil Sturgeon's Cache library. I think it will save you a couple of roundtrips to the database.

Mischievous, I didn't mean that you should put the values in a file instead of in the database.
I meant that you should store it in the database, but then cache it. If you do queries that give the same results every time (like getting some setting from the database that doesn't change often), you save the results of the query to a file. When an administrator changes the value (through a cms, I would guess?) you make sure you throw away that file.
In your application, when you need to get the variable you first try to get it from the file. If the file does not exist, get it from the database and save it to the file so next time you won't have to go to the database.
I haven't looked into the cache library n0xie points to, but I'm sure that does pretty much the same as I just decribed. So yes, definitely check it out. I will too Smile

Theme © iAndrew 2016 - Forum software by © MyBB