• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Efficiency question: Add a field or use a function

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table with information about employees. Each empl receives a salary and I need to show the cumulated salaries over a period of time - for every employee.

so imagine this, every time you ignite the program you see a list of empls (100+) and their cumulated salaries.

Question: Should I add a column cumulatedSalary to the table (and every time an empl recieves a salary - add it to the comulted field) or simply to use the SQL aggregate function to sum up their monthly salary..

What is more efficient?
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you expect to read this data many more times that you update it, then overall denormalizing it is more time efficient. However, there's more to life than just time efficiency. The demormalized solution uses more space. It's harder to maintain, since it will require an update trigger. It's not clear that the time gain is worth these costs.

The usual development approach is to keep the data normalized, and only denormalize if you encounter a performance problem. In other words, to apply the usual advice about avoiding permature optimization.

If you do decide you want this kind of denormalization, see if your database supports materialized views. This might entail less maintainence than a trigger.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Loren,

thank you for your reply; but could you be more specific when you say: denormalizing.

simply put, would you go on an aggregate function or add extra field?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
Denormalizing means to add an extra field.

I agree with Loren that we can't give you an efficiency answer without more information. You should use the function unless it is demonstrated to be a performance problem. How often do you run the program? How critical is response time?

An extra field will cause updates to the table to take longer. This is another cost that needs to be evaluated.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
Normalization is part of the termnology associated with relational database theory. There is a lot of information about it on the Internet. Here are the results of a search I did:

http://tinyurl.com/5aahl

As Loren says, everything is a trade-off. What you gain in performance, you pay for in extra disk space. You need to find the optimal solution, given your requirements and limitations. As Jeanne says, if you want us to help you find your optimal solution, then you need to supply us with details of your requirements and limitations.

Good Luck,
Avi.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic