Keeping transactions short.

[Riaan]

Hi

Every article on Interbase performance I've seen emphasizes short
transactions. What is the best way I accomplish this when displaying data in
a DBGrid, using IBX 6.06 / BCB 6 / IB 7.1 (dev). In my existing application
the transaction stays open all the time the user has the DBGrid open - is
there a better way?

Regards

Riaan

[Wayne Niddery [TeamB]]

Post by Riaan
Every article on Interbase performance I've seen emphasizes short
transactions. What is the best way I accomplish this when displaying
data in a DBGrid, using IBX 6.06 / BCB 6 / IB 7.1 (dev). In my
existing application the transaction stays open all the time the user
has the DBGrid open - is there a better way?

Yes, this is what ClientDatasets/DatasetProviders can do for you. It is more
plumbing, but it allows you to open and fetch the records from the query and
immediately close it - and the UI is driven by the cached records in the
ClientDataset. Then when changes are complete, the ClientDataset can send
all the changes back and, in a single transaction, the DatasetProvider can
apply the necesary changes.

[Riaan]

Thanks Wayne

I was expecting this answer, but I was hoping for an 'easier' solution.

I've seen a post in this newsgroup that the IBClientDataset component (I
assume from the IBX components) is no longer available in BDS2006. Is this
correct, and if so, what are the alternatives (I still have TIBClientDataSet
on BCB6 but I am concerned about future compiler support)

Riaan

Post by Wayne Niddery [TeamB]

Post by Riaan
Every article on Interbase performance I've seen emphasizes short
transactions. What is the best way I accomplish this when displaying
data in a DBGrid, using IBX 6.06 / BCB 6 / IB 7.1 (dev). In my
existing application the transaction stays open all the time the user
has the DBGrid open - is there a better way?

Yes, this is what ClientDatasets/DatasetProviders can do for you. It is
more plumbing, but it allows you to open and fetch the records from the
query and immediately close it - and the UI is driven by the cached
records in the ClientDataset. Then when changes are complete, the
ClientDataset can send all the changes back and, in a single transaction,
the DatasetProvider can apply the necesary changes.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"In a tornado, even turkeys can fly." - unknown

[Wayne Niddery [TeamB]]

Post by Riaan
I've seen a post in this newsgroup that the IBClientDataset component
(I assume from the IBX components) is no longer available in BDS2006.
Is this correct, and if so, what are the alternatives (I still have
TIBClientDataSet on BCB6 but I am concerned about future compiler
support)

The TIBClientDataset (all TXXXClientDatasets) were just composites of
TClientDataset/TDatasetProvider/TIBDataset, but without the control over all
the separate components and features. Some people had success with them but
there were issues and overall it's better to stick with the separate
components.

[Riaan]

Wayne

I have the following connection of components in place. Is this what you had
in mind?

TIBDataBase<-TIBQuery<-TDataSetProvider<-TClientDataSet<-TDataSource<-TDBGrid.

Regards

Riaan

Post by Wayne Niddery [TeamB]

Post by Riaan
I've seen a post in this newsgroup that the IBClientDataset component
(I assume from the IBX components) is no longer available in BDS2006.
Is this correct, and if so, what are the alternatives (I still have
TIBClientDataSet on BCB6 but I am concerned about future compiler
support)

The TIBClientDataset (all TXXXClientDatasets) were just composites of
TClientDataset/TDatasetProvider/TIBDataset, but without the control over
all the separate components and features. Some people had success with
them but there were issues and overall it's better to stick with the
separate components.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The two most abundant elements in the universe are hydrogen and
stupidity." - Harlan Ellison

[Wayne Niddery [TeamB]]

Post by Riaan
TIBDataBase<-TIBQuery<-TDataSetProvider<-TClientDataSet<-TDataSource<-TDBGrid.

That's the correct linking. By default, when you open the TClientDataSet, it
wil cause the TIBQuery to run (via the TDataSetProvider) and all records are
fetched and delivered to the TClientDataSet, the TIBQuery is closed and the
transaction committed.

[Riaan]

Thanks Wayne

One last question on this topic.

If I have a read-only dataset, is it possible to present the data in
data-aware controls after committing the transaction and without using the
TClientDataSet?

Riaan

Post by Wayne Niddery [TeamB]

Post by Riaan
TIBDataBase<-TIBQuery<-TDataSetProvider<-TClientDataSet<-TDataSource<-TDBGrid.

That's the correct linking. By default, when you open the TClientDataSet,
it wil cause the TIBQuery to run (via the TDataSetProvider) and all
records are fetched and delivered to the TClientDataSet, the TIBQuery is
closed and the transaction committed.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist." - Ayn Rand

[Wayne Niddery [TeamB]]

Post by Riaan
If I have a read-only dataset, is it possible to present the data in
data-aware controls after committing the transaction and without
using the TClientDataSet?

Commit closes connected datasets. For Interbase there is CommitRetaining you
can use, this allows Interbase to release certain resources but leave the
transaction (and thus connected datasets) open. Eventually you still need to
close this transaction with Commit, but it's not so bad to leave it open
awhile.

An important note: TIBTransaction defaults to "snapshot" isolation - which
means, for as long as that transaction is open, Interbase must keep versions
of every record changed/added/deleted. If you need to leave a transaction
open for any length of time, set it to be Read Committed. This reduces the
versioning - once another transaction has committed, records changed there
are allowed to be seen by your transaction and versions can be released if
no other transactions require them.

Finally you can also use a separate transaction that is set to read-only as
well as read committed for the purpose of read-only displays, this further
helps interbase handle things more efficiently.

[Jeff Overcash (TeamB)]

Post by Riaan
Thanks Wayne
One last question on this topic.
If I have a read-only dataset, is it possible to present the data in
data-aware controls after committing the transaction and without using the
TClientDataSet?
Riaan

If you are using IB 7.5 or higher then you can set the read commited transaction
to read only and then just leave the transaction open all day long. Read only
read committed transaction have no impact on the server any longer.

[Jeff Overcash (TeamB)]

Post by Wayne Niddery [TeamB]

Post by Riaan
TIBDataBase<-TIBQuery<-TDataSetProvider<-TClientDataSet<-TDataSource<-TDBGrid.

That's the correct linking. By default, when you open the TClientDataSet, it
wil cause the TIBQuery to run (via the TDataSetProvider) and all records are
fetched and delivered to the TClientDataSet, the TIBQuery is closed and the
transaction committed.

Don't forget to set the AutoStopAction to saCommit.