At 11:10 AM 04-04-02 +0000, you wrote:

> > >I have a database with about 250 tables, and 2300 views.
> > >
> > >When dropping a view, the "COMMIT RETAINING" part of the process is
> > >taking about 3 seconds each. Searching through the code I've been
> > >able to trace this to the function "isc_commit_retaining" that is
> > >called after executing the "DROP VIEW" statement.
> > >
> > >If anyone is going to ask why so many views, it's because it's
>taking
> > >too long to delete a view for the user to wait.
> >

I wrote:

> > Forgive the question, but are you saying that you have a user
>application
> > that creates and drops views?
> >
>
>Yes. Why is this a problem? I've been doing this in Oracle and I
>never had a problem. I didn't know I shouldn't do this.

Oracle and InterBase, unlike Access, are SQL databases. I don't (wouldn't)
do what you are doing in Oracle, either - not just because of (a)
performance issues but because of (b) the effects of multiple clients
creating and destroying metadata and (c) the notion of users creating ad
hoc metadata objects at all.

If you need to have users defining data sets for inspection, reports, etc.,
have them select parameters for client-side objects (queries) representing
the selected output. If you want these objects to be retrievable at some
later time, set up a table in which to store query definitions, which your
application can retrieve and recreate on demand. Include a key to identify
the owner of the definition, so that each user can retrieve his/her own
definitions. Unwanted definition records can simply be deleted from the
table (a data operation) without making and breaking metadata. It also
means you can avail yourself of the benefits of IBOQueries, something your
view-approach denies you.

For an example of the creation and storage, look at the Query Forms tab of
the IB_SQL application. There, Jason stores the definitions in the user's
Registry; but to store them instead in a database file would be very
simple. You would just have your "Save Query" button get the active
statement's SQL property and stream it into a text blob as a parameter for
your INSERT statement.

As to the matter of CommitRetaining, I don't believe any IBO code is
performing CommitRetaining on a DDL statement - it is not possible, since
no database cursor is involved. I'd suspect that the CommitRetaining
pertains to something else, e.g. the output set for the view still being
open whilst you attempt to destroy the underlying view. This is probably
causing havoc.

regards,

Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com