dynamic or pre calculate data

a bit new to programming and had a general question that I just thought of.

Say, I have a database with a bunch of stock information and one column with price and another with earnings. To get the price/earning ratio, would it be better to calculate it everyday or to calculate it on demand? I think performance wise, it'd be quicker to read only but I'm wondering if for math type functions its w开发者_运维问答orth the batch job to pre-calculate it(is it even noticeable?).

So how do the professionals do it? have the application process the data for them or have it already available in the database?

The professionals use a variety of methods. It all depends on what you're going for. Do the new real ratios need to be displayed immediately? How often is the core data changing? Ideally you would only calculate the ratio any time the price or earning changes, but this takes extra development, and it's probably not worth it if you don't have a substantial amount of activity on the site.

On the other hand, if you're receiving hundreds of visits every minute, you're definitely going to want to cache whatever you're calculating, as the time required to re-display a cached result is much less that recreating the result (in most scenarios).

However, as a general rule of thumb, don't get stuck trying to optimize something you haven't anticipated any performance issues with.

It would be good to keep statistical data as seperate table as those read only mode. you could calculate avarage, max, min values directly with SQL functions and save them. In mean time, for current period(day), you could dynamically calculate and show it. These statistical information can be use for reports or forcasting.

Pre-calculated value is (of course) faster.

However, it all depends on the requirement itself.

• Does this value will be invoked frequently? If it's invoked frequently, then using a precalculated value will bring a huge advantage.
• Does the calculation really need long time and/or huge resource? If so, using a precalculated will be helpful.

Please bear in mind, sometimes a slow process or a large resource consumption is caused by the programming implementation itself, not by a wrongly designed system.