--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
wrote:

>
> > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@>
> > wrote:
> > >
> > > > > >What if start transaction & commits are not written

explicitly

> > > > (since
> > > > > >the query in question is a select query)? Will they still

work

> > > > > > implicitly?
> > > > >
> > > > > I'm not sure if you're aware of but FB needs a transation

even

> > for
> > > > read only
> > > > > operations (like SELECT), so whether or not

you've 'explicitly'

> > > > started one,
> > > > > the transaction was created (by your 'connection

components')

> > and
> > > > (hope)
> > > > > commited at some point.
> > > > >
> > > > > Regards,
> > > > > Anderson
> > > > >
> > > >
> > > >
> > > >
> > > > I see.
> > > > In our case, as i had earlier mentioned, each query would be
> > within
> > > > its own transaction. When we execute the same query for say

40

> > > > times, the number of i/o reads is comparabable to the number

of

> > i/o
> > > > writes. However, when the loop goes to around 10,000, the

number

> > of
> > > > i/o writes is very much larger than the number of i/o reads.
> > > > Any clue?
> > > >
> > > > Bipasha
> > > >
> > >
> > > are you committing each transaction before starting the next?

or

> > are you
> > > leaving the first transaction open until the last is ready to
> > commit?
> > > and that's all you're doing in this 10,000 cycle loop?
> > >
> > > start transaction
> > > select data
> > > commit transaction
> > > loop
> > >
> > > ?
> > > Alan
> > >
> >
> >
> > Yes, only the query is being looped in this 10,000 loop.
> > We are not starting & committing the transaction explicitly,

since

> > it is only a select query.
> > To measure the I/O activity we are checking the I/O read/ write
> > values against the firebird service (fbservice.exe) in task

manager

> > while the query is in execution.
> >
> > Bipasha
>
> so:
> 1 transaction (even though before you said each query had its own
> transaction)
> 10,000 select statements which are not prepared once and

parametised, but

> prepared 10,000 times as well, yes?
> Does the select query have a limiting where clause?
> or an order by clause on an unindexed field?
>
> (BTW use GFIX to set FW on a database)
> Alan
>

We are not using any prepared statements.
There is a where clause. The query woud be something like this:
SELECT col1, col2, col3 etc. from table1 t1 where t1.col4 = (SELECT
col1', col2', col3' from table2 t2 where t2.col4' = xyz and t2.col5'
= abc)
But there is no order by or group by clause.

bipasha