Subject Re: performance issue when adding tables
Author mspencewasunavailable
--- In, Robert martin <rob@...> wrote:
> Hi
> We have a screen in our app that creates tables (DDL) to use on a
> temporary basis. As FB doesn't yet support temp tables we are
> the removal of said tables. However we don't do it well. This
leads to
> us having a large number of unused tables in our DB at times (we
> eventually remove them). However this is not the main issue, it
> compounds it.

AIUI, FB can only create 2 ^16 tables ever without doing a
backup/restore, so creating and dropping tables could leave
you with a database that won't work. (Anybody who actually knows
about this can jump in any time...)

There are also warnings about mixing DDL and DML.

> IBO collects a schema of the db and whenever DDL changes take
place the
> schema gets completely regenerated. This is a real problem for us
as it
> adds a large amount of delay after creating a new temp table. I
> this is an issue regardless of whether or not the schema is cached
> don't cache). The large number of tables makes this process quite
> (seconds ?)
> In the help it says I might be able to use executeImmediate to do
> ddl without forcing a rebuild of the schema. However my guess is
> if I do this than IBO won't 'see' the newly created temp table
(that I
> need to use).
> Does anyone have a suggestion as to how to get around this?

I have 15 or 20 such tables in my application. I added a field named
SESSION_ID to them all, and create them once.

When my application instance starts, I do this in the Data Module:

select gen_id(session_id_gen,1) as SESSION_ID

(where session_id_gen has been created, of course) and store the

Whenever I want to add a row to one of these tables, I use various
means to make sure that the SESSION_ID field gets populated, e.g.,
using a parameter, a macro substitution or just generating the
appropriate string directly (depending on circumstances) for an
IBOQuery or an AfterInsert event or just a direct assignment to the
field for an IBOTable.

For such tables, or queries that refer to them, I set the
OnPrepareSQL event to point to this routine (in my Data Module):

procedure TStartupDM.RestrictToSession(Sender: TObject);
(Sender as TIBODataset).SQLWhereItems.Add('SESSION_ID = ' +

(If you need to have a prepared query that gets executed a lot with
different parameters, then I suspect you would want to do this a
little differently.)

Finally, at the point where the original code dropped the temp table,
I call another routine in the Data Module to clean out the
appropriate rows by executing a query like this:

qry.SQL.ADD('DELETE FROM ' + tablename + ' WHERE SESSION_ID = ' +

I'm using the TIBO objects, but I suspect this will all work with
the TIB_ objects as well.

Michael D. Spence
Mockingbird Data Systems, Inc.