Your second view is doing a cartesian join of all rows in the target
table against all rows in the user_credentials table, then filtering
down by the user ID. With a small number of users, this is wasteful but
not too bad. But the resource and performance hit is geometric with the
number of users, so it is quickly prohibitive.

The first query acts first to limit the number of users joined to
include only the current user, before performing the join.

My past experience is that this is not a good place for a view. A view
must be executed fully before any application filters are applied, in
any architecture. You are better off writing a parameterized query
(PreparedStatement) like this:

select t_foobar.*,
user_credientials.user_id
from t_foobar,
user_credientials
where t_foobar.Credientials = user_credientials.credientials;
and user_credientials.userid = ?

* In platforms other than Java, you may use the notation :userid instead
of the ? placeholder.

On Tue, 2005-02-08 at 18:27, Christian Andersen wrote:
>
>
>
>
> 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@...
>
>
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>