Optimization: Where to process data? Database, Server or Client?

Question

I've been thinking a lot about optimization lately. I'm developing an application that makes me think where I should process data considering balancing server load, memory, client, loading, speed, size, etc..

I want to understand better how experienced programmers optimize their code when thinking about processing. Take the following 3 options:

• Do some processing on the database level, when I'm getting the data.
• Process the data on PHP
• Pass the raw data to the client, and process with javascript.

Which would you guys prefer on which occasions and why? Sorry for the broad question, I'd also be thankful if someone could recommend me good reading sources on this.

Answer 1

Database is heart of any application, so you should keep load on database as light as possible. Here are some suggestions

1. Get only required fields from database.
2. Two simple queries are better than a single complex query.
3. Get data from database, process with PHP and then store this processed data into temporary storage(say cache e.g. Memcache, Couchbase, Redis). This data should be set with an expiry time, expiry time totally depends upon type of data. Caching will reduce your database load to a great extent.
4. Data is stored in normalized form. But if you know in advance that data is going to be requested and producing this data requires joins from many tables, then processed data, in advance, can be stored in separate table and can be served from this table.
5. Send as few as possible data on client side. Less HTML size will save bandwidth and browser will be able to render page quickly.
6. Load data on demand(using ajax, lazy loading etc), e.g a image is not visible on a page until user clicks on a tab, this image should be loaded upon user click.

Answer 2

Two thoughts: Computers should work, people should think. (IBM ad from the 1960s.)

"Premature optimization is the root of all evil (or at least most of it) in programming." --Donald Knuth

Unless you are, or are planning to become, Google or Amazon or Facebook, you should focus on functionality. "Make it work before you make it fast." If you are planning to grow to that size, do what they did: throw hardware at the problem. It is cheaper and more likely to be effective.

Edited to add: Since you control the processing power on the server, but probably not on the client, it is generally better to put intensive tasks on the server, especially if the clients are likely to be mobile devices. However, consider network latency, bandwidth requirements, and response time. If you can improve response time by processing on the client, then consider doing so. So, optimize the user experience, not the CPU cycles; you can buy more CPU cycles when you need them.

Finally, remember that the client cannot be trusted. For that reason, some things must be on the server.