One big query vs. many small ones?

Question

I'd like to know, which option is the most expensive in terms of bandwith and overall efficiency.

Let's say I have a class Client in my application and a table client in my database.

Is it better to have one static function Client.getById that retrieves the whole client record or many (Client.getNameById, Client.getMobileNumberById, etc.) that retrieve individual fields?

If a single record has a lot of fields and I end up using one or two in the current script, is it still better to retrieve everything and decide inside the application what to do with all the data?

I'm using PHP and MySQL by the way.

Playbill

It depends... by everything you do mean everything required right? What's your bandwidth? How many users do you have? Is this all in the same server-room? Down fiber-optics? Over mobile internet? What's your latency? Where are your users? Where are your servers? How powerful are they? How are they organised? How many bytes is "a lot"?... – Ruffner

@Ruffner Do you have any capacity for abstract thought? I'm asking which option is better in the general case. The application isn't released yet so I don't know – Playbill

Yes :-). @oded, is correct the fewer calls to the database the better, the less bytes transferred the better, but all the questions are still valid; they will have an impact... – Ruffner

@Ruffner You're right, the best optimization one could do is actually checking what is(are) the bottleneck(s) in the application using real life information after all, but as a general case I guess Oded's answer is the correct one – Playbill

If you haven't come across ORM I highly suggest taking a look at the Doctrine project. It automatically links your classes to your tables and generates getter/setters so you don't have to even think about this step. Why reinvent the wheel? – Astrionics

Answer 1

Is it better to have one static function Client.getById that retrieves the whole client record or many (Client.getNameById, Client.getMobileNumberById, etc.) that retrieve individual fields?

Yes, it is.

Network latency and lag as well as the overheads of establishing a connection mean that making as small a number of database calls as possible is the best way to keep the database from saturation.

If the size of the data is really so much that you see an effect, you can consider retrieval of the specific fields you need in one single query (tailor the queries to the data).

Watt

The quote has 2 alterantive perspectives separated with an 'or'. "Yes, it is" isn't really clear. – Estis