At 03:28 PM 29/01/2003 +0100, you wrote:

>Hello IB-Support!
>
>I need setting up isolation level for select statement, that shall
>showing rows in DBGrid.
>
>Which level is faster and not hard overhead in multi generation
>architecture
>
>1. Read commited
> a) read only
> b) read & write
>2. Snapshot
> a) read only
> b) read & write

Transaction isolation is not a performance-tuning option: it determines
how the view of the database state which is seen by one transaction is
affected by changes which are committed by others.
-- a read-committed transaction can requery the database (i.e. "refresh"
the output set of a query) and see the results of changes committed since
the last time it refreshed
-- a snapshot transaction can never see the effect of commits that occurred
since it began

Each has its own practical advantages.
-- Read-committed is good for an interactive task, where users are
inserting sets or selecting sets at random, for updating (editing) or deleting.
-- Snapshot is good for a task that needs a stable view of dataset state
throughout the life of the task: reporting, data export, stocktake

Coming to the issue of read-only vs read-write: if a transaction is
wrapped around a task that does not involve adding, changing or deleting
the data it touches, then read-only reduces the overhead on the server
because it reduces the amount of "book-keeping" that the server needs to
do. You can sometimes get a slight improvement in the impact of background
running of huge reports, exports, etc. by running the process in a
read-only transaction. This is hard to quantify, though, since there are
other factors in client and metadata architecture that have bigger impact
on performance.

>Refresh rows is programmed as follow:
>
>{begin of code}
>if Transaction.InTransaction then Transaction.Commit;
>Transaction.StartTransaction;
>
>ibquery.sql.text:='...select statement...';
>ibquery.open;
>{end of code}
>
>But if is read commited faster, I can drop that part of code, which
>start new transaction.

This is a very costly approach to refreshing a dataset, regardless of which
isolation level you use. "Ad hoc" sql statements that are created on the
fly will cause a lot of overhead in the client/server interface.

If your task needs to get a continual fresh view of committed work then you
need to use Read Committed. This allows your application to requery the
data (Close and Open the dataset, in IBX terms) without ending the
transaction and starting another one.

If the dataset's SQL specification is not going to change (that is, it
always shows the same columns, in the same order, with variations only in
which records are selected) then you should make the SQL static in your
application and use parameters to apply values to your WHERE clause.

This rule applies, regardless of transaction isolation. The difference is
that, with Snapshot, you will have to end the transaction and start a new
one, in order to get an updated view of the database state. With Read
Committed, a requery is sufficient to refresh the view. The traffic across
the wire is the same in both cases.

The big performance cost in *your* model comes from applying the SQL
property every time, since it requires two queries EVERY time you refresh
your dataset: one to prepare the query (where the client passes the
statement across to the server for validation and returns a structure which
your application has to read and respond to), and the other to submit the
actual request for data or an operation.

In your example, even though you have hard-coded the SQL, the client
doesn't know that it is being asked to apply an identical specification,
and so it goes through the whole expensive process of clearing the
structures in which it stores the validation data and then calling Prepare
to repopulate them - hence, two queries every time where only one is needed.

If you can avoid changing the SQL specification of a query, then it is
prepared only once and stays prepared: subsequent executions of the query
do not perform that validation step.

heLen