In PHP, how many DB calls per page is okay?

Question

I've got shared hosting on a LAMP set up. Obviously the fewer calls to the Db per page the better. But how many is too many? Two? Ten? A hundred? Curious what people think.

Answer 1

I think your current number of queries is okay as long as the servers (web and database) can handle all your requests and return a page in a acceptable time. It depends on the servers to a large scale. Nevertheless using as few queries as possible is a good rule anyways.

Answer 2

That really depends on your (db)servers setup. Try to cache most of information as possible and reduce db calls to a minimum. A database will (almost in every case) be the bottleneck of your service - the higher the usage of your site. So whatever you do try to avoid fireing a query as if not really necessary.

I try not to use more than 10 db calls per page, but that really depends on your infrastructure and the information you want to provide.

Answer 3

I would say that depends on the server load. If you have 1 visitor per minute, then 1-10 db calls per page would be just fine. If your server load is higher than that, say 10 page requests per second, then you should consider caching to minimize the load on your db server.

Answer 4

Okay, that makes sense. So I watch my speeds and then look at optimization. Good to know. Thanks!

Answer 5

One or less is always best. Two is usually one too many.

If you can return multiple result sets in a single query, then do it. If the information is fairly static, then cache it and pull from cache.

10 separate database calls is not good, but its not going to kill a low usage site.

Answer 6

When I worked on the www.boxman.com project in the .com boom, they had one website that appeared as 9 different language/country sites under different domains. Every piece of text was pulled from the DB as well as usual things like products etc... Each page typically would involve 200 odd DB requests, but mainly returning a single id,string combo. We had 100 of users on the system at a time.

The DB ran DB2 SQL on a 16 way RS6000 unix box. This is probably equivient to a modern day 3ghz QUAD core intel box.

The system worked... as volumes picked up I implemented a cache which involved writing a sync process that moved data that was static on a daily basis to the webserver's drive so it no longer hit the DB.

Basically I would say if performance is okay then it's okay! but you should allow for expanding demand and be ready for it when it happens.

Answer 7

@Anthony So it can be done. It just shouldn't. :-)

Answer 8

Another important matter except for caching is to use prepared statements. When you execute a query, the db has to 1) analyze the query and 2) execute it. If you use prepared statements, the db can cache the query plan it used last time, so each query will be a smaller burden on the dbms. Don't count the load in how many queries you execute, but how much stress you put on the dbms. Executing 100 prepared queries can be faster than executing 50 queries generated ad-hoc in the code.

Answer 9

Good advice. Cheers.

Answer 10

Don't forget

1. use stored procs - they run faster.
2. run them in fresh - once a week. (the database optimizes stored procs using its current state. If this changes then the store process will stop being optimial).
3. use commands like "show plan" to really understand what your SPs are doing.
4. Stored procs can return multiple datasets (datatables) this cuts down on network traffic. A single stored proc can do multiple things.

Tony

Answer 11

remember 100,000 page requests is only just over 1 a second over 24 hours. As long as they all don't request at once.

Answer 12

How long is a piece of string? How long should a man's legs be? How many DB queries should you make on a page load?

There's no single answer. Obviously, making unnecessary queries is a bad idea. Starting excessive DB connections is even worse. Caching unchanging values is good. Beyond that, you can't really arbitrarily say "You should only use $N queries" on a page - it depends on what you're trying to do & what your performance goals are.

In theory, any application could be written to use a single DB query - even if that query is a massive 20-way join involving unindexed full table scans and return thousands of rows that are mostly nulls that would take ridiculous ammount of memory and time to process once it gets to your application. Clearly, this would be a Very Bad Thing. In general, avoid doing things that are obviously wasteful (like doing a bunch of single-row queries in a loop) and worry about performance later.

In the words of Donald Knuth "We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil". Everyone talks about 'scalability' like they're really going to be the next Twitter but, in reality, if Twitter had focused on being as big as they are now, they probably never would've gotten a product out the door in the first place.