Subject Re: [IBO] View & Query or Query Only ???
Author Svein Erling Tysvær

this type of questions really belong to the ib-support list, not the
ibobjects one. I'll give an answer anyway, but if you have any further
questions - please direct them to ib-support.

>I have a general question. Is there a benefit to using views on tables
>with no joins?

I would say the main benefit would be to hide information - i.e. if you do
not want all users to have access to everything, you could grant them
access to a subset of records or columns through views. Possibly you could
have some benefit if needing aggregate results as well.

>For example, if I have a query that calls for active employees, then
>need to query from those active, would it be better to ...
>1) create a view that first grabs all the active employee, then to run
>my query on the view; or

Normally I'd say this isn't worth it, but I'm not a frequent user of views
and others may disagree. And you should anyway consider such a view. Will
the view be used in many different queries - if so, it might help reducing
the chance of errors because your selects may be simpler. If you e.g. mark
records as deleted rather than physically delete them, I would say that a
view based on the SQL "SELECT <fields> FROM <table> WHERE DELETED IS NULL
OR DELETED = 'FALSE'" could be useful because it is always easy to forget
to eliminate deleted records when dealing with the table directly.

>2) simply run the query on the table with multiple clauses in my Where

I prefer this - possibly excepting where the SQL becomes so complicated
that programmers get lost (but if this happens with only one table involved
and no self-join, it would be better to replace the programmer and/or your
design ;o).

>Which is faster? And, any other comments would be appreciated.

I think the table approach is never slower, but that the view could be
equally fast in most circumstances. When a query against a table isn't fast
enough and you cannot further improve it through changing your query or
defining/changing/deleting indexes, then a stored procedure is the next
thing to try.