Proposed guideline: Process data close to the source.

[BezPowell]

This is in some ways related to a few of the other guidelines, but might be worth mentioning as its own topic.

Proposed new guideline:

Process data close to the source.

All information passed between the layers of an application incurs a cost, both in terms of data transferred, and CPU cycles for (de)serialisation. Wherever possible, data transformations should be performed close to the source to reduce these costs and avoid processing data that will later be discarded.

Success Criterion - Perform filtering on the database.

When retrieving information from a database, perform all filtering and field selection in SQL. Only retrieve the values that you need and avoid reliance on framework helpers that might defer filtering to later on in the process.

Benefits

Environmental:

Filtering out unneeded data at a deeper level of the application may reduce energy usage, as less processing is required for (de)serialisation.

Performance

Relational databases and other specialist data stores are generally heavily optimised for data filtering and retrieval. Performing transformations at this level of the application may lead to reduced CPU time and faster responses.

[AlexDawsonUK]

I agree entirely with this one, though do you have any third-party links to support the guideline which we could include as reference material (we try to have weighted evidence whenever possible)?

[BezPowell]

Hi Alex. I don't remember any references off the top of my head, but will try and dig some out this week. This has mainly come up recently for me with laravel, and I do seem to remember reading a few articles that had benchmarks in them.

[BezPowell]

Very basic, but does actually have some benchmarks: https://onextrapixel.com/mysql-has-functions-part-5-php-vs-mysql-performance/
Hopefully, I'll be able to find some other sources with actual figures, but this seems to be one of those 'common knowledge' things where everyone knows aggregating data in SQL is more efficient, but doesn't actually have any numbers to back it up.

[AlexDawsonUK]

Thinking about this more, "only accessing the database once" is mentioned by 3.24 as are the perils associated with "repeated requests" for information (also note we don't just want to be SQL explicit as there are other database types on the Web).

I think the best course of action may be todo the following:

• The SC and Benefits should be merged into 3.24 as it is largely covered in the Database Guideline.
• The content on "Process data close to the source" has a wider appeal (beyond databases) and possibly falls into Edge-Computing which might work well in 4.10 as a new SC dedicated to tightening the relational stack.

I'll see what I can cook up (noting this useful link on Query perf) for the next draft.

[AlexDawsonUK]

The new SC and content update have been integrated into the living draft.
They will be published along with other changes in the next version of the specification.