Using Tables for Fast Math

[caption id=“attachment_950” align=“alignright” width=“300”]A plaque demonstrating conversions between one foot, two feet, an imperial yard, and a meter. Convert all the things![/caption] Relational databases are great for storing structured data. But the data we store doesn’t have to be user entered data. What if we use tables in the database to precompute the output of some kind of calculation?

Our Conversion Table

In this case, we’re doing a simple unit of measure conversion table. Pretty much everybody apart from America uses the metric system, so we’ll create a simple table that lets us look up conversions. https://gist.github.com/peschkaj/06a0f10250bdc1966134#file-convert-01-sql The next step to do is add some sample data so you can see how this works. For our purposes, we’re just going to add conversions from feet to meters and meters to feet. There are far more complex conversions that we could be doing, but this is simple enough for our purposes. https://gist.github.com/peschkaj/06a0f10250bdc1966134#file-convert-02-sql Now that we have data in our table, we can go about converting from meters to feet: https://gist.github.com/peschkaj/06a0f10250bdc1966134#file-convert-03-sql This is pretty simple, but it illustrates the point - we can pre-compute values to make the application run faster. This can make life significantly easier when we’re dealing with complex conversions. These conversion rates don’t change. The formula stays the same even though the inputs stay the same.

What About More Complex Math?

There are many more opportunities to pre-compute data in the real world. You can look at things like trigonometry tables, pressure conversions, or loan amortization schedules. These are all fixed formulas based on known constants. This is, in essence, a form of caching. In the programming world, caching the results of a function is known as memoization. By pushing this technique down to the data layer, we’re able to precompute more permutations than we could reasonably hold in memory on an application server. Yes, we’re taking up a few extra bytes of disk space, but we can save tremendously on various complex calculations that, frankly, should only happen once.

“Standard Measure” by Johnnie Walker is licensed under CC BY-NC 2.0