Win a copy of Spark in Action this week in the Open Source Projects forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Efficiency question: Add a field or use a function

 
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?
 
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?
 
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • 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.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • 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.
 
Nothing up my sleeve ... and ... presto! A tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic