6

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.

federico-t
  • 12,014
  • 19
  • 67
  • 111
  • 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"?... – Ben Mar 24 '12 at 21:42
  • @Ben 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 – federico-t Mar 24 '12 at 21:48
  • 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... – Ben Mar 24 '12 at 21:52
  • @Ben 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 – federico-t Mar 24 '12 at 22:01
  • If you haven't come across ORM I highly suggest taking a look at the [Doctrine project](http://www.doctrine-project.org/). 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? – Sam Selikoff Apr 04 '13 at 15:43

1 Answers1

15

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).

Oded
  • 489,969
  • 99
  • 883
  • 1,009