Following on from David's (good) advice.

If he wants to keep the code in the database rather than the
application, it can be done using stored procedures rather than
views. That gives the added benefit of "alter procedure" if you need
it in the future. With views, you need to drop them which may be a
problem if you have dependancies. I have had to run a 1000 line
script to add one extra line to a where clause in one view before.

I would also change your query to use the join syntax.

create procedure trythis
(
userid varchar(100)
)
returns
(
field1 varchar(100),
field2 varchar(100)
)
as
begin
for select tf.field1, tf.field2
from user_credientials uc
join t_foobar tf on (tf.Credientials = uc.credientials)
where user_credientials.userid = :userid
into :field1, :field2
do
begin
suspend;
end
end
^

Then you can run

Select field1, field2
from TryThis('Mike');

or even join it to another table or procedure depending on what you
need to achieve.

Adam

--- In firebird-support@yahoogroups.com, David Johnson
<d_johnson@c...> wrote:

> 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@d...
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >