Venus Software Operations wrote:

> On 04/03/2011 03:07, Milan Babuskov wrote:
>> 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:
>>
> Thanks a lot Milan for this idea.
>
> I am happy to go the View way too, if you can tell me how a view works,
> does it calculate once and update itself occasionally if the data it is
> dependent on changes. Or is it as same as running a SP or doing SQL-PT
> from the application, calculated everytime it is accessed, just more
> convenient.

It's similar to stored procedure.

Main advantage of view is that is can use indexed on underlying tables
while a stored procedure cannot. In the example procedure I gave in
previous message, if you wish to query only WHERE SKU = 1111, a
procedure would not use index on underlying tables unless you make SKU
an input parameter and specify SKU inside the procedure. If you wish to
make arbitrary WHERE clauses, then using VIEW is a better idea.

Main advantage of procedure is that you can program it to do something
else then just returning rows from a table.

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================