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 insert...select. 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) )
as
begin
... do the calculation...
SUSPEND;
end

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) );

insert into YEARLY_REPORT (SKU,QTY,PRICE)
select SKU,QTY,PRICE from YEARLY_REPORT_PROC;

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:
http://www.guacosoft.com/xmlwizard
==================================