Firebird performance issues.

We have just ported a database system which runs fine on Oracle and
MSSQL server to Firebird (1.5 and 2.0 beta) running on windows 2003
server.

The database in question does not expose its tables to the outside
world, the data is accessed through views.

We have 2 sets of views:
- one set of views for dedicated clients (client server application) and,
- one set for the non dedicated clients (browser interface).

The reason we need the views is that we have row level security. We do
this by having views like this.

create view foobar as
select *
from t_foobar
where credentials in (select credentials
from user_credientials
where user_credientials.user_id = current_user);

This works well and even performs quite well on firebird.

However for the web users we needed to do it in a different way.

create view web_foobar as
select t_foobar.*,
user_credientials.user_id
from t_foobar,
user_credientials
where t_foobar.Credientials = user_credientials.credientials;

This is so the web app can do things like "select * from web_foobar
where user_id = 'mike'" since the web app gets its connections from a
connection pool which all logs on to the server using the same user_id.

Once a query has more than 2 of the web views in the from clause
firebird grinds to a halt. For example, one query on mssql ran in
about 7 sec where as on firebird it took about five minutes.

After looking at the query plan I think I know where the problem is.

Are there any optimizer hints I can use to make firebird execute its
queries the way I want it to?

Cheers Christian Arild Stær Andersen

candersen@...