On Fri, 21 May 2004 12:49:32 -0400, rkusenet wrote:


> "Art S. Kagel" <kagel@bloomberg.net> wrote
>
>> On Fri, 21 May 2004 07:31:19 -0400, Markus Bschorer wrote:
>>
>> Table? Just create a table with the joined data columns and add triggers
>> to the underlying tables to maintain them live. Better than materialized
>> views.
>
> Could you explain how this is better than MV. The whole concept of MV is to

Not better, but since IDS does not have MV...

> avoid the cost of joining and aggrgrating big tables. That's why MV is

Hence the pre-built table.

> created as a point-in-time snapshot and the queries use it automatically
> when needed.

Trigger to keep it in sync with the live data. Since Markus states that the
tables we would use this on are fairly stable the overhead of the triggers to
keep it in synch would be FAR lower than the overhead of a true VIEW.

> If you are going to have a trigger, then the same performance penalty is
> introduced. In that case why bother about any new table. Just let the SQL do
> the work at query time. or am I missing anything.

Same answer to the last two questions. The overhead of using a trigger to
replicate the mods to a relatively quiet table to the 'view-table' will be
minor compared to repeating the query overhead each time the data is needed
from a true VIEW. If the tables are actively updated, then the added
overhead of the view-table combined with the 'cost' of the data getting
out-of-synch but depended on, is too high to violate the Normal Form rules in
this way. But with static or low activity tables that are frequently joined,
the cost savings would out weigh the additional headache.

And this join-table idea is BETTER than MV because the data is live and
up-to-date.

Another option occurs to me. IDS 8 supports pre-join indexes which are VERY
efficient for frequently joined tables. If you are using IDS 9.xx one could
implement something like that using the VII or perhaps even functional index
feature which would give the performance benefit of MV with NO additional
overhead beyond index maintenance.

Art S. Kagel