Thanks Ian and all you people who answered my post. I decided to merge all my
tables into one big table, and go for views and sp's to do all the "dirty" work,
as suggested. The fact of using multiple outer joins that don't
use indexes at all on aggregate queries seems inefficient. Just one more thing:
I'm using IBExpert and IBConsole for FB administration, but it seems that
memory is not released after a series of queries, I have to disconnect and
reconnect to get all my RAM released. Is it something with IBExpert, IBConsole,
FB, or is it just M$ windows ?

Thanks. Rodrigo.

Mensaje citado por "Ian A. Newby" <iann@...>:

>
>
> Hi Rodrigo,
>
> > select
> > coalesce(a1.id,a2.id,...,a12.id) as id,
> > sum(iNvl(a1.value,0)+iNvl(a2.value,0)+...+iNvl(a12.value)) as value
> > from table1 a1
> > full outer join table2 a2 on a1.id = a2.id
> > full outer join table3 a3 on a1.id = a3.id
> > ...
> > full outer join table12 a12 on a1.id = a12.id
> > group by coalesce(a1.id,a2.id,...,a12.id)
>
> I believe that with your sql construction firebird is going to be
> unable to use indexs at all.
>
> The best approach to me would be to merge all the tables into one,
> create views based on that table if you require them to duplicate the
> 12 original tables.
>
> Alternatively, you could use a stored procedure similar to this:
>
> create procedure getData
> returns (id integer, value double)
> as
> for select id, sum(value) from a group by id into :id, :value do
> suspend;
> for select id, sum(value) from a1 group by id into :id, :value do
> suspend;
> { ... }
> end;
>
> then use the following sql to get the results:
>
> select id, value from getdata() group by id;
>
> I not promising it will be any faster however, but I think it will be!
> I must emphesis that my first option is to me a far better solution (
> I believe you could make the views updateable in this option)
>
> Regards
> Ian Newby
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.