Milan Babuskov wrote:

> Anyway, after the conversion, the main problem with FB were views (and
> STILL are). They have a lot of UNIONS and work painfully slow on FB
> because FB doesn't use indexes. That customer still complains that FB is
> much slower than MSSQL. For those interested, if you have a view like this:
>
> create view v1 (x, y, z)
> as
> select x, y, z from table1
> union all
> select x, y, z from table2;
>
> Both table1 and table2 have index their X field. Now, when you run:
>
> select * from v1 where x = 10;
>
> The plan is NATURAL for both tables. In the database we migrated from
> MSSQL those tables have millions of records. The queries on MSSQL
> sometimes work 1000 or more times faster. I guess I don't have to
> explain a benefit of index to anyone here.
>
> For some uses we rewrote some procedures to use:
>
> create procedure p1 (x integer)
> returns (y, z) as
> begin
> for select y, z from table1 where x = :x
> into :y, :z
> do suspend;
>
> for select y, z from table2 where x = :x
> into :y, :z
> do suspend;
> end
>
> This works really fast, but we can't cover all the possible combinations
> as there would be hundreds of procedures (some of the views have 20+
> columns).
>
> As I understand some FB2.x version should fix this and I'm eagerly
> awaiting...

Have you considered triggers, Milan? I.e. have a table populated through
triggers on table1 and table2 rather than a view. Sure, it is a
workaround of a problem soon to be solved anyway, but unless there are
problems with the table size, it should be a lot simpler than writing
hundreds of procedures and indexes will be no trouble.

Just a thought,
Set