Subject Re: [ib-support] Meaning of transaction in relation to the prepare statement
Author Stan
Hi All

There is no way to share prepared statements across connections?

I have a multi-threaded "data-inserter". Each thread creates
its own connection, prepares ~10 statements, executes them
a bunch of times and cleans up.

I have problem, where 4-10 threads hang on the "isc_prepare_statement"
call for 20-60 seconds, when I have 50 simultaneous threads trying to
prepare their statements. Other threads prepare their statements
in under 1 sec, but also seem to hang on the actual statement
executions. Once the stuck isc_prepare_statements return, it
proceeds beautifully fast.

Is this a problem with the Server? Ideally I would prepare the
statements once for all the connections and then just batch execute
them in different transactions.

any other suggestions to get around this hang?

FB 1.5.3 and FB 2.0
Windows XP Pro SP2
Athlon X2 4400
2 GB Ram.
2 80gb sata drives in RAID 1.



--- In, "Dimitry Sibiryakov" <SD@...>
> On 21 Jan 2003 at 11:36, Ponze, Bart wrote:
> >I am having some trouble understanding the use of transactions in
> >relation to the isc_dsql_prepare() API call.
> You have to provide isc_dsql_prepare() with a transaction handle
> because of metadata versioning of IB. Engine must know which version
> of metadata must be used with your statement.
> > I want to prepare a
> >statement once (a sql statement with parameters - "update table set
> >field=? where key = ?" for example) and then execute it many times
> >over a long period of time from many different transaction contexts.
> You can use a prepared statement in any transaction as long as:
> 1) the table has the same structure, visible from the transaction;
> 2) the statement hasn't been freed by isc_dsql_free_statement with
> DSQL_drop;
> 3) the statement hasn't been freed automatically on disconnect.
> I successfully did it.
> >prepare statement and I am experiencing hangs on the commits and start
> >transactions. A transaction on the isc_dsql_execute() makes sense to
> >me but what does it mean on the prepare? If it matters, I am using the
> >FB 1.5 Alpha 5 Win32 SS on WinXP. Thx.
> Try your program against FB 1.0. This way you can tell if this is a
> bug in your program or in FB 1.5 engine.
> If you manage to make a simple test program that surely hanging,
> post it into Firebird-devel list.
> SY, Dimitry Sibiryakov.