Thread: views: performance implication

We hope to use views as a way to give customers odbc based ad-hoc query
access to our database while enforcing security. The reason is that we do
not want to put data into separate tables by customer, but rather use a
customer ID as part of any query criteria on any table.

So the question is: are there any negative performance implications of
doing so (other than the obvious of having more data in a table than is of
interest to the querying customer)? Back in the old days, views were a
performance no-no in Informix, so I want to be sure we aren't setting a big
trap for ourselves.

Thanks in advance.

John Moore

http://www.tinyvital.com/personal.html

UNITED WE STAND

On Thu, 4 Jul 2002, John Moore wrote:

> We hope to use views as a way to....
> So the question is: are there any negative performance implications of
> doing so ... Back in the old days, views were a
> performance no-no in Informix, so I want to be sure we aren't setting a big
> trap for ourselves.

Yes, there may be. There are some cases where the optimizer won't
optimize a view the way it will optimize an equivalant query. (I posted
a query about this a while back--I don't remember whether it was here
or on -hackers.) So after creating a view, you'll want to check with
EXPLAIN that you're still getting the same query plan.

But I don't think this is true of every view, just some of them.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC