CODEWITHSUNDEEP
sqlperformance
Nikita Silverstruk
Nikita Silverstruk

Reputation: 1117

What is the most efficient way of providing data calculations? Saving all at once or calculating on request?

For example, I have a table CALCULATE with fields val1, val2, val3.

Now, I have two options of storing/retrieving the data:

  1. Add columns addition multiplication, division and save the calculated data on INSERT

  2. Save the data first and then on select return the calculated values.

Also, for the option 1, would using a procedure or a trigger be more efficient?

This is a very simple example, what I am trying to find out is on a much bigger scale, with more complicated formulas and more ammount of data, which way would be more efficient?

Upvotes: 0

Views: 177

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You are trading off storage size for computational complexity. As you add more columns into your table, you increase the size of the table and the amount of I/O needed to read the entire table.

If you are typically doing queries that require full table scans, then the increased data size is an issue.

On the other hand, storing the pre-calculated values in the table has several advantages:

  1. The calculations are done only once per row. So, if you are retrieving them many times, you get a savings.
  2. You can index the calculated columns (in any database) making your queries more efficient.
  3. If your queries are "needle-in-the-haystack" queries (that is, they just fetch a few rows at a time), then there is no impact on performance.

The biggest issue with pre-calculation is maintaining the calculated values. A typical approach is to use "before update" and "before insert" triggers to do the calculation. Alternatively, you can wrap all inserts and updates in stored procedures and place such business logic there (that is what I normally do).

The difference in performance between a stored procedure and trigger should be totally negligible under most circumstances. If you are trying to optimize performance on a high-throughput environment, then you should ask this question at dba.stackoverflow.com, with a lot more detail on the nature of the problem, your database, and your hardware.

Upvotes: 1

cosmos
cosmos

Reputation: 2303

If your DB size is big enough, option 2 will be beneficial. As, logically, you will run more select queries on the table than insert queries.

Upvotes: 0

Related Questions