Should I Transform My Data In My SQL Query?

Published on: 2020-07-29

Watch this week’s video on YouTube.

Imagine you need to join two tables of data and filter the results. Perhaps you also need to convert some of the values for display as well (eg. 0 => “No”, 1 => “Yes”).

Do you choose to perform all of this in your SQL query? Or do you bring the data into your app and and handle it there with code?

Option 1: Write it in a SQL query

Usually I’m inclined to do as much in the SQL query as possible because:

Optimization

Relational SQL databases tend to be optimized for doing things like joins, filtering, aggregations, etc….

Software engineers have spent decades tuning their sorting algorithms and squashing bugs to make their relational databases handle these operations efficiently. Sure, you can probably find the occasional edge case where you could handcraft some app code to accomplish one of these things faster, but in most cases it’s not worth the additional time and effort.

Ordered Data

The types of operations in the example above (joins, filtering) benefit from ordered data.

Indexes in your database offer data stored in a predefined order, making all of those operations faster. If you need to perform a transformation that can utilize an index, it usually makes sense to let the database handle this operation in the SQL query rather than moving that data to your app and not have an index available.

Data Reduction

Joining and filtering your data in your database allows you to send a reduced number of records over the network to your application.

Network speeds tend to be one of the largest bottle necks in application systems, so eliminating the amount of data you need to pass through them up front in your SQL query can greatly improve the performance of your application.

This is especially true if you have switched to a work-from-home world where network speeds are even more of a bottleneck.

Hardware

This one can be a toss up. The SQL database servers I usually work with are beefy, having significantly more memory and CPU than my general purpose application servers (or my laptop running analytical code).

This means I can run most of my transformation logic faster on my database server than I can anywhere else. This comes at the cost of your performance hungry queries taking away resources from other queries that may be running on the database server at the same time, but on servers not running at capacity this trade off can be worth the speed. More on this in our app code option.

Portability

If you need to share your transformation logic, a SQL query is going to be easier to share with others or include in other processes than application code.

For example, SQL queries are like a universal language in most companies: programmers, analysts, data scientists, technical product managers, and anyone else who may be interested in understanding the business logic will be able to get a high-level understanding by looking at the SQL query. It will also be easier for them to incorporate into their processes.

Contrast that with some app code that is not easy to execute without installing dependencies and understanding language specific syntax; a SQL query will always be easier to share and reuse.

Option 2: Code it in the application

Everything you can write in a SQL query can be performed in most programming languages.

Let’s discuss when it makes sense to store transformation logic in app code.

Domain Specific Performance Improvements

Most SQL relational databases are built to be general purpose. That’s what makes them so powerful: they work well to help solve many different problems without needing domain specific optimizations.

Sometimes though, you may know something about your data that will allow you to work with it more efficiently than a relational database.

For example, maybe your data consists of mostly XML files, or you know you will be doing a lot of recursive processing of your data. Sure, most relational database engines have some way to accomplish these tasks, but performing this type of processing will most likely be faster to do in your application code.

Local Caching

Sometimes your application will need to process and reprocess the same data over and over again.

In these cases, it may make sense to transfer your data across the network a single time to your application, so it can locally process (and reprocess) that data as many times as needed.

Costs

Many enterprise database platforms cost money to license. Application servers usually don’t have those same kinds of costs. That means that running your transformation logic in a SQL query on your database server is often more expensive than in code on an application server.

If your database server is not at full capacity, then this likely isn’t an issue – after all you want to maximize the usage of the licenses you are paying for.

However, if your database environment is at or over capacity, offloading some transformation logic into your application code may make it run faster and will also likely be cheaper to run.

The cloud makes this case even more transparent, with database as a service offerings generally being more expensive than their application compute counterparts.

Conclusion: which is better?

It depends on your situation.

This post speaks to generalized scenarios. For specific uses, there will be times where it makes sense to store your logic in SQL queries, and other times in your application code.

The point is the next time before you dive in and start writing code, think through your restrictions and goals to figure out the best location to run things.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

One thought on “Should I Transform My Data In My SQL Query?”

  1. As you say – it always depends.

    Usually I prefer to transfer the data in their original datatype to the application and add e.g. formating there. Of course you could use CONCAT / CONVERT / FORMAT etc. direct in your SQL query to output 123.45 USD, but I guess most times it is much more flexible (and faster) to send the 123.45 as DECIMAL / MONEY and (if necessary) the currency as VARCHAR to the app and let it format the output (maybe it needs the value multiple times with different formatings or allows custom format settings)….

    Another example:
    you have a list of e.g. 400 servers and a user may have an account on one or more of those servers. If I want to allow the user to switch the server and / or create accounts on another one), I could of course join the global server list with the user account list and send it to him / her (and his million fellow users).

    Or I could prepare / select / cache only the global server list, that will be send to the app only once per day (or app restart). This list can be used for every of the other million users too. And I could make a very small and fast query over the account table to give him a list of the few servers, where he has an account and let the app join the two result sets locally (and maybe sort the servers with existing accounts to the top of the list). I think this solution would be better on long term and save me maybe some dollars for my cloud vendor…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.