Subject Re: [IBO] performance issue when adding tables
Author Robert martin
Hi Jason

I posted the following a while ago. I am just reposting it in case you
missed it. A number of people have made suggestions about not creating
multiple temp tables, however my issue is the performance hit I take
every time I run a DDL statement (when Ibo needs to update its cache).
Do you have any suggestions ?

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496

Wild Software Ltd

Robert martin 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 handling
> 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 do
> eventually remove them). However this is not the main issue, it just
> compounds it.
> 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 believe
> this is an issue regardless of whether or not the schema is cached (we
> don't cache). The large number of tables makes this process quite slow
> (seconds ?)
> In the help it says I might be able to use executeImmediate to do the
> ddl without forcing a rebuild of the schema. However my guess is that
> 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?