> Is there a performance difference between commit
> and commitretaining on a transaction

...

> Does the transaction isolation level make a difference as well?

To the best of my knowledge/experience (I am not an expert on the
internals of the IB/FB engine) there is very little difference.

You may like to check the CommitAction property on any queries you are
using. It defaults to caClose, but if you have set to other option you
may have an impact on performace during commit.

Make sure you are using as many different transactions as is
appropriate to your application requirements. If for example you have
one transaction for many queries, and have commitaction set to
something other than caClose, then a commit may result in a lot of
work as all the various queries are processed.

> We seem to find lengthy delays operating over a phone
> line making sure everything is committed before
> running a new query. We think it may be due to
> exessive commits rather than commitretainings.

Keeping transactions as short as practical will help the server
maintain its efficiency, but dont get carried away trying to commit
after every minor change (as that is going to be less efficient for
the client and network).

For example; if you are importing records dont try to commit after
every insert. I've imported hundreds of thousands of records before
calling commit, with no obvious impact on server efficiency (although
a rollback after such an import can take a long time :-).

So use transactions as they are intended - to ensure an entire block
of work is saved or cancelled as a single block of work. Choose the
isolation according to how you want different work blocks to interact.


And finally... Run and watch the IB_Monitor while performing your
commit, it can show all interactions with the server, which will give
you an idea what is happening and how long each item is taking.

hth

--
Geoff Worboys - TeamIBO
Telesis Computing