Subject Re: [firebird-support] Data of multiple years and opening stocks & balances
Author Milan Babuskov
Venus Software Operations wrote:
>> 1. Store the balances via a year end procedure.
>> 2. Compute them as needed.
>> I think it depends on the volume of data in the database. For a small
>> database I would be tempted to compute opening balances and for a large
>> database I would store them at year end or whenever required.
> Thanks Thomas for your views. I am torn between, I do not want to store
> calculated values, yet I need to deliver efficient reporting.

You can define a VIEW (or a stored procedure) that calculates the values
dynamically. One day, if data gets to big, you can rename the
view/procedure, create table using the same name and fill it in
initially using This way you don't have to change the
application code. I had something like that in one of my databases,
here's an example:

create procedure YEARLY_REPORT returns (
SKU integer,
QTY decimal(18,3),
PRICE decimal(18,2) )
... do the calculation...

All the application code does "select from YEARLY_REPORT where ...".

Now, imagine you have a lot of data and this procedure is slow. Create
another identical procedure with a different name:

drop procedure YEARLY_REPORT;
create procedure YEARLY_REPORT_PROC returns ( ...etc.

create table YEARLY_REPORT (
SKU integer,
QTY decimal(18,3),
PRICE decimal(18,2) );


then add indexes to the table to increase speed even more.

Milan Babuskov

The easiest way to import XML, CSV
and textual files into Firebird: