Place business logic in the application, not the database
We have all done it. We needed to extract data from an SQL database, and the query we needed to extract data from was a complex query with multiple table joins, complex filter criteria, and fancy functionality.
And why not? SQL databases such as MySQL and PostgreSQL are really good at performing complex joins, filtering and sorting to get exactly the data you need from a query and nothing more. Let the database do the work! It’s certainly a lot easier than having to write that logic in application code, isn’t it?
By performing these operations inside the database, what you are really doing is pushing the business logic of the application into the bowels of your database. You take this business logic out of your application code and move it into database logic instead.
The result? You end up using more database resources and less application server resources in order to get the data recovery results you are looking for.
The same is true when implementing data insertion requirements. What happens when you want to insert data into your database? Rather than verifying that the data you insert is valid, conflict-free, and completely correct in the application before performing the data insertion, you let the database perform the validation. You place these conflict criteria in the database schema requirements and let the database throw an error (if detected) in the data you insert. You can do this, for example, by using
UNIQUE constraint criteria and indices.
Application resources are easier to scale
Now, before a lot of database engineers harass me on Twitter saying, “That’s exactly what these features are for!” – or many developers protesting that “some things have to be done this way” – let me be clear. I To do understand that there are cases where you need your database to do these kinds of validations and checks.
So I’m not saying your database doesn’t do any application logic. What I’m saying is you should make your database do as little application business logic as possibleand do as much application business logic in the application code itself as possible.
Why am I saying this? Because in general it is much easier to scale your application server resources than to scale your database server resources.
It’s almost a universal truth. For most web applications, when traffic increases, you can usually easily add application servers to handle increased load. But additional application servers aren’t useful unless your database can also scale to handle the increased load. And scaling an SQL database is considerably more difficult than adding application servers.
Take a look at the resources available for your app. You should think of your database as being made up of scarce compute resources, while your application code layer (including services) is made up of readily available compute resources that can be easily extended.
Database resources are scarce. Compute resources are readily available.
Once you understand this mindset, you’ll realize that putting as much logic as possible into your app layer will facilitate easier scaling. Integrating business logic into your database tier severely limits the scalability of that business logic.
Let the database handle complex filtering
You can’t always put all the business logic in the application layer. Sometimes you need to place some queries in the database tier. A valid reason for this may be to control the amount of results returned. Consider a query that has complex filter criteria on a very large database table:
select * from mytable where
The expected results, after running the complex filter query on a large data set, may represent only a few rows of the table. the
* will return all data from just these few rows. But if you want to run the complex filter query in the application layer instead of the database instead, you usually need to fetch all the data from the database first. You may end up with something like this:
select * from mytable
This would return all data from mytable to the application layer. The application layer would then be casual all rows that did not meet the filter criteria it performs on the data. The problem with this is that all the contents of mytable must be transferred to the application layer to perform this request. For a large data set, this is unacceptable.
But, in many cases, simply refactoring a query or series of queries can avoid problems like this and allow you to execute more logic in the application without excessive data traffic. One way to do this is to separate data filtering from data retrieval.
Separate filtering from recovery
Very often we combine the concept of filtering results and retrieving results in a single query. Especially when we’re looking at a large table with a lot of data, it’s very convenient to write a query that does all the filtering and specification we want to select the rows we need, and then have the query return all the data we need. need from the selected Lines. We do something like this:
select * from mytable where
By itself, that sounds OK. But when the query involves complex joins or other complex operations, it can impose a rollback load on the database, which strains database resources.
One way around this problem is to make an initial query that simply returns only the fields required for the filter query of all rows, then perform the filter logic in the application. Suppose field1 and field2 are the fields actually involved in the
select id,field1,field2 from my_table
Then we can run complex filter query logic on field1 and field2 in your application code. This will result in a list of IDs for the rows in mytable that match the complex query. Once you have the list of matching IDs, you perform a trace query that gets the actual data from the prefiltered rows:
select * from my_table where id in (3,5,123,392,...)
Both queries are very simple queries to execute: no complex operations need to be performed in the database. The business logic needed to select the data to return is performed in the application layer, but there is very little superfluous data that needs to be transferred from the database to the application. By splitting the query into separate filtering and data retrieval queries, you’ve refactored your query and allowed complex, resource-intensive business logic to run in the application instead of the database.
Avoid performing operations on returned results
Another easy way to move business logic from the database to the application layer is to avoid performing calculations on the results returned to the database; perform them in the app instead. So rather than doing something like this:
select POWER(SQRT(field1)*SIN(field2),5) from my_table where ...
You can do it:
select field1,field2 from my_table where ...
And then perform the equivalent of
POWER(SQRT(field1)*SIN(field2),5) in the application with the results returned. The result is that all the calculations needed to perform the calculations use readily available application resources, rather than scarce database resources.
Move joins into app layer
Complex joins are another area where extensive database resources may be required. Rather than joining data in the database, move as much of that joining logic as possible into the application layer. By refactoring your code in this way, you can significantly reduce the load on your database, while increasing your scalability.
Break the chains
Of course, you can’t always refactor your queries this way. Sometimes you just need to perform a complex query in the database itself. But, by removing as many of these complex queries as possible, you reduce your dependence on scarce database resources and increase your dependence on highly available application-level resources.
So the next time you look at that big, long, gnarled query that uses multiple joins and complex filtering logic, don’t be proud of it! Instead, look at ways to replace it with simpler queries, perhaps multiple queries, and business logic that runs in your application layer.
As your application evolves, you’ll appreciate the improved flexibility.
Copyright © 2021 IDG Communications, Inc.