Optimization: where to process the data? Database, server or client?
I've been thinking a lot about optimization lately. I am developing an application that makes me think of where I should process the data, given the load balancing of server, memory, client, load, speed, size, etc.
I want to better understand how experienced programmers optimize their code when they think about processing it. Take the following 3 options:
- Do some processing at the database level when I receive data.
- Process data in PHP
- Passing raw data to the client and processing with javascript.
What would you guys prefer in what cases and why? Sorry for the broad question, I would also be grateful if anyone could recommend me good reading sources on this one.
source to share
The database is the heart of any application, so you should load the database as easily as possible. Here are some suggestions
- Get only required fields from the database.
- Two simple queries are better than one complex query.
- Get data from a database, process it with PHP, and then save the processed data to temporary storage (say a cache like Memcache, Couchbase, Redis). This data must be set to expire, the expiration time depends entirely on the data type. Caching will significantly reduce the load on the database.
- The data is stored in a normalized form. But if you know in advance that the data will be queried, and a join from many tables is required to create that data, then the processed data can be stored in a separate table and can be sent from that table.
- Send as little data as possible to the client side. The smaller the HTML, the less bandwidth will be, and the browser will be able to render the page quickly.
- Load data on demand (using ajax, lazy loading, etc.), for example, the image is not displayed on the page until the user clicks on the tab, this image should be loaded when the user clicks.
source to share
Two thoughts: computers must work, people must think. (IBM announcement from the 1960s).
"Premature optimization is the root of all evil (or at least most) in programming." --Donald Knuth
If you are not going to, or are going to become Google or Amazon or Facebook, you should focus on functionality. "Make it work before you do it quickly." If you plan on growing to that size, do what they did: drop equipment 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 usually best to host intensive tasks on the server, especially if the clients are most likely mobile devices. However, consider network latency, bandwidth requirements, and response times. If you can improve response times by processing on the client, then consider doing this. Thus, optimize user experience, not processor cycles; you can buy more cpu cycles when you need them.
Finally, remember that the customer cannot be trusted. For this reason, some things must be on the server.
source to share
So, as a general rule, process as much data as possible in the database. The cost of creating a new connection with a request is very high, so you want to limit it as much as possible. Even if you have to write very ugly SQL, execution JOIN
will almost always be faster than 2 SELECT
statements.
PHP should really only be used for formatting and caching data. If you do a ton of data manipulation after every request, you are probably storing your data in a format that is not very practical. You want to cache anything that doesn't change often in a near server-ready state using something like Redis or APCu.
Finally, a client should never perform data operations on multiple objects. You never know the availability of customer resources, so always save customer data. Paginate and sort on any dataset larger than a few dozen in the background. An AJAX request using AngularJS is typically as fast as doing a sort on over 100 items on an iPad 2.
If you would like more information on any aspect of this answer, please ask and I will do my best to provide examples or additional information.
source to share