connection pool: difference between tables and views?

I'm trying to solve an issue where I'm working with a Sybase database and Sun's app server. For reasons that are beyond my control, the java application looks at updateable views that map 1:1 to the database tables. The views aren't used to combine data from multiple tables, each view simply contains the identical data found in every corresponding table.

I think, but I am not sure, that I have an abnormally large number of connections being used. All the connections are being closed properly, I'm just showing 60 thousand of them created in the past few weeks when we typically have 15 users on at most at any given time. I suspect that we are not using pooling properly, but I also want to know if pointing to the updateable views rather than to the tables directly has anything to do with that.

Thank you in advance.

In your case you may have many number of views created and that are accessed by the users a lot.

View is a virual table, it is not physical table in the database.
Views pull information from various tables. Accessing one view may need to read from various tables in your database. this takes long time and leads to performance issue.

A view can be updated by user after providing privileges from administrator.

hope this helps you..

Thanks.

View is a stored query. A query is just fired that yields to result from one
or more table. The scenario is bit obscure to me. I doubt if that is anything
to do with using table for viewing the data or using the view.
[ October 17, 2008: Message edited by: Chandra Bhatt ]

Alan,
Some databases have a "materialized view." This is a view that stores/caches a copy of the query and is used when the query is expensive. Do you know if you have a materialized view or a regular view?

Originally posted by Jeanne Boyarsky:
Alan,
Some databases have a "materialized view." This is a view that stores/caches a copy of the query and is used when the query is expensive. Do you know if you have a materialized view or a regular view?

I doubt it is a materialized view because I run queries against the views and with unmodified data the performance is sluggish unless I re-execute the identical query right away. If I wait more than a few seconds, the query response is sluggish. However, I'll ask the DBA just to be sure.

Originally posted by Gopikrishna Madishetti:
In your case you may have many number of views created and that are accessed by the users a lot.

Yes. 100+ views each one mapping to a single table.

Originally posted by Chandra Bhatt:
The scenario is bit obscure to me. I doubt if that is anything
to do with using table for viewing the data or using the view.

It is a very odd set-up, but its the one I'm stuck with. OK, if the views are unlikely culprits, I'll start looking around elsewhere in the project.

Thank you all!
[ October 17, 2008: Message edited by: Alan Smithee ]

Alan, I see that you are working with updatable views.

You should work with your DBA to see if the problem is related to your application (if these views are in production, it is probably your app's error) or if it is indeed an error with the views.

For those that may not understand the term, an updatable view is a view that allows you to apply insert, delete, and update statements to modify the underlying table.

Any changes made in an updatable view always pass through to the base table unambiguously.

What does unambiguously mean in this context? It means that each row in the view associates with exactly one row in the base table.

This means the following must be true for a table to qualify as updatable:
[LIST]The view must be defined over only one table (no joins).
The view can not have group by or having clause (one view row to one base row rule)
Select Distinct is not allowed (one view row to one base row rule)
Union, intersect, and except are not allowed
No aggregation
No calculated columns
If the underlying base of the view is also a view, it must also be updatable (yeah, duh I know).

The rest of rules vary by DBMS, but those are the basic ones.

Jeanne may or may not find this interesting... materialized Oracle views can also be updatable (you have to include the For Update clause).

You can't use an alias for your columns, either (at least not in Oracle).

[ October 17, 2008: Message edited by because I can't spell

: Paul Campbell ]
[ October 19, 2008: Message edited by: Paul Campbell ]

Originally posted by Paul Campbell:
Jeanne may or may not find this interesting... materialized Oracle views can also be updatable (you have to include the For Update clause).

I do find that interesting. Thanks for sharing!