--- In ib-support@y..., Helen Borrie <helebor@t...> wrote:

> At 10:32 PM 25-07-02 +0000, you wrote:
> >Can anyone help?
> >
> >I am getting the above message in my application. I'm not sure what
> >to look for and will be happy to give further relevant information

if

> >I know where to look.
> >
> >The application is failing after a scanning only 125 records and
> >populating an additional field by referencing through a library
> >procedure that queries a table in another database using a three
> >field match. The query table is not large - 600 records or so.
> >
> >There is a severe degradation in speed towards the end so it seems
> >that there is a memory implication. The query routine table is
> >closed after each call.
> >
> >I'm not sure that it is relevant but I'm using IB Objects -
> >IB_Query's.
> >
> >How can I increase the size of the Optimizer Block or what

mitigating

> >steps can I take to prevent such an occurrence.
>
> It's not something I have ever heard of as configurable. It's not
> something I have ever encountered in 7 years with

InterBase/Firebird and

> three years with IBO...but it sounds like a message you would get

when your

> SQL plan is too large to fit in the buffer allocated for it. The

cure for

> that would not be to increase the size of the buffer but to improve

the plan.

>
> So, far, you haven't supplied any comprehensible information about

the SQL

> you are trying to perform. Once you provide that, we'll have

something to

> work on...
>
> Use the IB_monitor to capture the PLAN and include that, too,

please.

>
> heLen
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org ·
> http://users.tpg.com.au/helebor/
> _______________________________________________________

Dear Helen,

Thanks for responding. I will admit my knowledge of
Interbase/Firebird and IBObjects as being comparitively very limited
and its helpful to know that the problem has not been encountered.

I have solved the problem. I'm not sure that this has anything to do
with PLAN's etc.

All I was attempting to do was:-

1. Iterate through Table A
2. Call a routine on another form with Query on Table B against
criteria derived from Table A. The Query was opened and closed on
each occasion as it would normally be called from other parts of the
application just once.

This approach caused the Size of Optimizer Block exceeded error.

I went back to basics and created a specific solution for the bulk
run keeping Queries A & B open - ie opening just once - and the
problem is solved.

However this leaves me with a niggling doubt as to whether repeated
individual calls to the routine are over a busy operator day going to
cause the Size of Optimizer error. Would this indicate some sort of
memory leak?

My general approach is to keep queries open for the minimum amount of
time - I thought this was good practice - but in the light of
my 'experience' I'm not sure.

In view of the above I'm not sure that any further detail is
necessary but if you still think that its related to the PLAN then
I'll gladly provide further information.

Thanks again for your help.

Francis Moore





2.