Board index » delphi » Delphi7 + dbExpress + Interbase6 : performance degradation

Delphi7 + dbExpress + Interbase6 : performance degradation

For the last year, we (70 users) used a D5 application using BDE to connect
to Interbase 6. Now, the application is rewritten in D7 using dbExpress. The
database is remained the same.
Since we moved from D5 to D7, we experience an overall faster access to
data, but not for long.

Before, the server was always available at all time; we didn't reboot it for
weeks.
Now, we have to reboot the server every day, because there is a huge
performance degradation during the day. A query we run takes at 9am 2
minutes, but at 5pm that same query takes at least 10 minutes. Overall
performance drops to a fraction of normal. The windows task manager,
performance tab shows much more cpu usage. The cpu usage is (almost)
completely due to ibserver.

A bit of information:
Server
Interbase 6.0.2.0
Server PIII-1000 dual proc (ib_affinity)
Win 2000 SP2
1 GB RAM

Clients
PIII or higher, Win 2000 on all PC's, except for 2 who run Win98.
Some Clients connect via Citrix Metaframe XP
D7 + dbExpress
SQLconnection - SqlDataSet - DatasetProvider - ClientDataset
properties of SqlConnection:
SQLDialect = 1
CommitRetain = False
WaitOnLocks = True
TransIsolation = Readcommited

Database
Dialect = 1
Pagesize = 8192
Oldest snapshot = 565000
Oldest active = 566000
Next transaction = 580000
Sweep interval = 20000

I don't know if the gap between oldest and next transaction is normal,
because frankly we didn't check it before. The combination D5-BDE-IB6 just
kept running as it was supposed to be.
I've checked all kinds of sites and newsgroups, but found no solution. We
experience similar behaviour as with 'phantom users'.

We're willing to upgrade to IB7, if it will solve our problems. What is
causing the problem? D7, dbExpress, IB6 or me (params,...)?

Any help is appreciated,

Patrick Beckers

 

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Your description sounds like a transaction is remaining open for a
long time. Wait until the database is slow (5:00 PM) and dump the
header statistics using gstat or IBConsole. Post the header statistics
so we can look at them.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Quote
"Patrick Beckers" <patrick.beck...@rentacar.be> wrote in message

news:3e5cf217$1@newsgroups.borland.com...

Quote
> For the last year, we (70 users) used a D5 application using BDE to
connect
> to Interbase 6. Now, the application is rewritten in D7 using dbExpress.
The
> database is remained the same.
> Since we moved from D5 to D7, we experience an overall faster access to
> data, but not for long.

A major difference between the BDE and dbExpress is that the BDE would wrap
every access to the database in a transaction for you if you did not control
the transaction explicitly, while with dbExpress you must make sure you
handle transactions yourself. It is almost a guarantee your problem is a
transaction being left open in the application

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman
.

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Thanks for the response.

I don't use explicit transactions, but leave it to dbexpress.
Just to make sure, I checked a record-update with sqlmonitor:
the post begins with 'isc_start_transaction', then you have the actual
record posted, and finally ends with 'isc_commit_transaction'. I believe
this is done by the dataset provider.
Unfortunately, not all modifications are done through dataset providers.
Sometimes, I do inserts and updates through the
SqlDataSet.ExecSql command. I'm not sure if this is OK because I don't see
the 'isc_start_transaction' and 'isc_commit_transaction', although changes
are succesfully written to Interbase.

Thinking of it, if transaction are made implicit by SqlConnection, could it
then be that when you end the application abnormally (eg. Ctrl-Alt-Del), a
pending transaction remains? If so, is it possible for Interbase to 'see' it
(connection lost, missing heartbeat,...) and to take measures?

Hope this will help. In a while, I'll post today's results as asked by Bill
Todd.

"Wayne Niddery [TeamB]" <wnidd...@chaff.aci.on.ca> schreef in bericht
news:3e5e0b05@newsgroups.borland.com...

Quote
> "Patrick Beckers" <patrick.beck...@rentacar.be> wrote in message
> news:3e5cf217$1@newsgroups.borland.com...
> > For the last year, we (70 users) used a D5 application using BDE to
> connect
> > to Interbase 6. Now, the application is rewritten in D7 using dbExpress.
> The
> > database is remained the same.
> > Since we moved from D5 to D7, we experience an overall faster access to
> > data, but not for long.

> A major difference between the BDE and dbExpress is that the BDE would
wrap
> every access to the database in a transaction for you if you did not
control
> the transaction explicitly, while with dbExpress you must make sure you
> handle transactions yourself. It is almost a guarantee your problem is a
> transaction being left open in the application

> --
> Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
> "Democracy, without that guarantee of liberty, is merely a method of
> selecting tyrants." - Alan Nitikman
> .

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


This is the first snapshot made at 07:30, just after reboot

Database header page information:
 Flags   0
 Checksum  12345
 Generation  583538
 Page size  8192
 ODS version  10.0
 Oldest transaction 583483
 Oldest active  583491
 Oldest snapshot  583490
 Next transaction 583524
 Bumped transaction 1
 Sequence number  0
 Next attachment ID 0
 Implementation ID 16
 Shadow count  0
 Page buffers  0
 Next header page 0
 Database dialect 1
 Creation date  Feb 6, 2003 6:12:34

    Variable header data:
 Sweep interval:  20000

This is the second snapshot, taken at 17:30.
Database header page information:
 Flags   0
 Checksum  12345
 Generation  597507
 Page size  8192
 ODS version  10.0
 Oldest transaction 583483
 Oldest active  583579
 Oldest snapshot  583491
 Next transaction 597493
 Bumped transaction 1
 Sequence number  0
 Next attachment ID 0
 Implementation ID 16
 Shadow count  0
 Page buffers  0
 Next header page 0
 Database dialect 1
 Creation date  Feb 6, 2003 6:12:34

    Variable header data:
 Sweep interval:  20000

(Un)fortunately, we didn't meet any major problems today. It's getting
slower, but still workable.

"Bill Todd" <b...@notthis.dbginc.com> schreef in bericht
news:35up5vc47240g0m1bpvcsju6l2rrpdpspq@4ax.com...

Quote
> Your description sounds like a transaction is remaining open for a
> long time. Wait until the database is slow (5:00 PM) and dump the
> header statistics using gstat or IBConsole. Post the header statistics
> so we can look at them.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


At 1730 the oldest active transaction is stuck at 583,579 while the
next transaction has advanced to 597,493, a difference of almost
14,000. This means that very early in the day transaction 483, 579 was
started and it has never been committed. There may be many other
uncommitted transactions as well. This is the cause of your
performance decrease.

When you use dbExpress and edit records in a ClientDataSet the
DataSetProvider starts a transaction, fetches the records, then
commits the transaction when you open the ClientDataSet. When you call
ApplyUpdates the provider also starts and commits a transaction
automatically. However, the provider only starts and commits the
transaction if there is no active transaction. I suspect that you are
performing updates that do no use a ClientDataSet and you are not
explicitly calling the SQLConnection's StartTransaction and Commit
methods. You will have to add these calls to your code.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Not with TClientDataSet.  As Patrick stated, he is also doing
transaction with the ExecSQL method.  I suggest, that when
you do these transactions, you use a separate TSQLConnection,
not the one the TClientDataSets are using and start and commit
transactions manually.  Your problem should then go away.

Quote
Bill Todd wrote:
> At 1730 the oldest active transaction is stuck at 583,579 while the
> next transaction has advanced to 597,493, a difference of almost
> 14,000. This means that very early in the day transaction 483, 579 was
> started and it has never been committed. There may be many other
> uncommitted transactions as well. This is the cause of your
> performance decrease.

> When you use dbExpress and edit records in a ClientDataSet the
> DataSetProvider starts a transaction, fetches the records, then
> commits the transaction when you open the ClientDataSet. When you call
> ApplyUpdates the provider also starts and commits a transaction
> automatically. However, the provider only starts and commits the
> transaction if there is no active transaction. I suspect that you are
> performing updates that do no use a ClientDataSet and you are not
> explicitly calling the SQLConnection's StartTransaction and Commit
> methods. You will have to add these calls to your code.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Thanks, this is a great help to me.
As you can see in my response to Wayne Niddery below, I have been doing some
checking with Sqlmonitor, and found out the DataSetProvider actually start
and commits a transaction when ApplyUpdates is called, but not the
SqlDataSet.ExecuteSql.

Is it OK to conclude:
1. I do not have to change my code when doing updates by ApplyUpdates on
ClientDataSet-DataSetProvider-SqlDataset
2. When performing other updates, eg through a SqlDataSet.ExecuteSql
command, I have to add explicitely a "start and commit" transaction
3. Reading data from the database doesn't require transactions.

Where do you get all this information? I've checked everywhere, including
manuals, internet,... Any good recommandations?

Thanks.

"Bill Todd" <b...@notthis.dbginc.com> schreef in bericht
news:0qns5vs0lpq20c9j9cg0kubbirs5l7cvdk@4ax.com...

Quote
> At 1730 the oldest active transaction is stuck at 583,579 while the
> next transaction has advanced to 597,493, a difference of almost
> 14,000. This means that very early in the day transaction 483, 579 was
> started and it has never been committed. There may be many other
> uncommitted transactions as well. This is the cause of your
> performance decrease.

> When you use dbExpress and edit records in a ClientDataSet the
> DataSetProvider starts a transaction, fetches the records, then
> commits the transaction when you open the ClientDataSet. When you call
> ApplyUpdates the provider also starts and commits a transaction
> automatically. However, the provider only starts and commits the
> transaction if there is no active transaction. I suspect that you are
> performing updates that do no use a ClientDataSet and you are not
> explicitly calling the SQLConnection's StartTransaction and Commit
> methods. You will have to add these calls to your code.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Quote
Patrick Beckers wrote:
> Thanks, this is a great help to me.
> As you can see in my response to Wayne Niddery below, I have been doing some
> checking with Sqlmonitor, and found out the DataSetProvider actually start
> and commits a transaction when ApplyUpdates is called, but not the
> SqlDataSet.ExecuteSql.

> Is it OK to conclude:
> 1. I do not have to change my code when doing updates by ApplyUpdates on
> ClientDataSet-DataSetProvider-SqlDataset

Yes

Quote
> 2. When performing other updates, eg through a SqlDataSet.ExecuteSql
> command, I have to add explicitely a "start and commit" transaction

Yes, and would recommend a separate TSQLConnection.  It would be nice
if you could program the Provider to always start its own transaction,
that way with drivers / databases that allows multiple transactions
per connection could share the TSQLConnection.  Threading issues also
come up when sharing one TSQLConnection.  The extra overhead has always
been worth the reliability to me.

Quote
> 3. Reading data from the database doesn't require transactions.

Yes

Quote
> Where do you get all this information? I've checked everywhere, including
> manuals, internet,... Any good recommendations?

Help with dbExpressPlus :-)   You will learn more about dbExpress then
you ever wanted to know!

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


On Thu, 27 Feb 2003 20:48:27 +0100, "Patrick Beckers"

Quote
<patrick.beck...@rentacar.be> wrote:
>Thanks, this is a great help to me.
>As you can see in my response to Wayne Niddery below, I have been doing some
>checking with Sqlmonitor, and found out the DataSetProvider actually start
>and commits a transaction when ApplyUpdates is called, but not the
>SqlDataSet.ExecuteSql.

>Is it OK to conclude:
>1. I do not have to change my code when doing updates by ApplyUpdates on
>ClientDataSet-DataSetProvider-SqlDataset

Correct.

Quote
>2. When performing other updates, eg through a SqlDataSet.ExecuteSql
>command, I have to add explicitely a "start and commit" transaction

Correct.

Quote
>3. Reading data from the database doesn't require transactions.

NOT correct. InterBase requires a transaction to read data. This is
part of the versioning mechanism that allows you to get a snapshot
view of data without preventing other users from doing inserts and
updates. If you are reading data with a ClientDataSet the provider
will start and commit the transaction for you. However, if you just
use a SQLQuery or SQLDataSet to select data and read through it you
need to start and commit a transaction. If you want to understand how
versioning works and why you need a transaction to read see
http://www.dbginc.com/tech_pprs/IB.html on my Web site.

Quote

>Where do you get all this information? I've checked everywhere, including
>manuals, internet,... Any good recommandations?

Depends on what information you are looking for. There are some good
papers on bdn.borland.com.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Quote
>> 3. Reading data from the database doesn't require transactions.
>Yes

Actually, that is not correct in the case of InterBase. IB requires a
transaction to read data. This is a requirement of the versioning
architecture so you can have snapshot isolation without blocking
inserts or updates.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Quote
"Patrick Beckers" <patrick.beck...@rentacar.be> wrote in message

news:3e5e3bfd@newsgroups.borland.com...

Quote

> I don't use explicit transactions, but leave it to dbexpress.
> Just to make sure, I checked a record-update with sqlmonitor:
> the post begins with 'isc_start_transaction', then you have the actual
> record posted, and finally ends with 'isc_commit_transaction'. I believe
> this is done by the dataset provider.

Correct.

Quote
> Unfortunately, not all modifications are done through dataset providers.
> Sometimes, I do inserts and updates through the
> SqlDataSet.ExecSql command. I'm not sure if this is OK because I don't see
> the 'isc_start_transaction' and 'isc_commit_transaction', although changes
> are succesfully written to Interbase.

This is where your problem will likely be. This is most likely resulting in
one very large transaction to cover these - started with the first of them
and not closed til the process is closed.  You need to wrap these
appropriately with explicit transasctions.

Quote
> Thinking of it, if transaction are made implicit by SqlConnection, could
it
> then be that when you end the application abnormally (eg. Ctrl-Alt-Del), a
> pending transaction remains? If so, is it possible for Interbase to 'see'
it
> (connection lost, missing heartbeat,...) and to take measures?

When Interbase discovers the client has gone away, it will roll back any
open transactions.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


I believe he was asking as it relates to dbExpress.  Yes the
TSQLConnection will start a transaction, but close it as
soon as all the records are retrieved to the TClientDataSet.

So don't do incremental fetches of records.  Or if you are,
make sure you retrieve every thing in one go.  Don't do
Retrieve as needed.

As the developer he absolutely does not need to start a transaction
for selects.

Quote
Bill Todd wrote:

>>3. Reading data from the database doesn't require transactions.

> NOT correct. InterBase requires a transaction to read data. This is
> part of the versioning mechanism that allows you to get a snapshot
> view of data without preventing other users from doing inserts and
> updates. If you are reading data with a ClientDataSet the provider
> will start and commit the transaction for you. However, if you just
> use a SQLQuery or SQLDataSet to select data and read through it you
> need to start and commit a transaction. If you want to understand how
> versioning works and why you need a transaction to read see
> http://www.dbginc.com/tech_pprs/IB.html on my Web site.

>>Where do you get all this information? I've checked everywhere, including
>>manuals, internet,... Any good recommandations?

> Depends on what information you are looking for. There are some good
> papers on bdn.borland.com.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Hi Bill, all,

Quote
"Bill Todd" <b...@notthis.dbginc.com> wrote:
> see http://www.dbginc.com/tech_pprs/IB.html on my Web site.

A very nice publication, explaining transactions thoroughly, a
'must read' for all serious DB developers.

If I had the time I would like to compare IB versioning to Oracle's
"read consistency" ... but I better spend the time in evaluating
IB/FireBird.

Thanks Bill

Georg
 ___   ___
| + | |__    Georg Rehfeld      Woltmanstr. 12     20097 Hamburg
|_|_\ |___   georg.rehf...@gmx.de           +49 (40) 23 53 27 10

Re:Delphi7 + dbExpress + Interbase6 : performance degradation


Hi,

I've been busy rewriting the app during the weekend, it's finished now and
it looks good. What i've done is:
- create a second SqlConnection
- connect every SqlDataSet that has an ExecSql to that second SqlConnection
- wrap every ExecSql with code to start and commit a transaction

I've been testing this and the transactions aren't stuck anymore. Today,
we're going to do some further testing in a multiuser environment (actually
just 2 users), but I guess this has solved our problem. The reason I didn't
wrap the read instructions is because I've been testing with 'InTransaction'
and don't see an active transaction. Is this the correct way to check if a
transaction is started?

Ans I want to thank everyone who participated in this thread. You're all
most helpful.

Patrick Beckers

"Bill Todd" <b...@notthis.dbginc.com> schreef in bericht
news:unvs5v07pargrl4viup0mp2f8sqhcdascd@4ax.com...

Quote
> >> 3. Reading data from the database doesn't require transactions.
> >Yes

> Actually, that is not correct in the case of InterBase. IB requires a
> transaction to read data. This is a requirement of the versioning
> architecture so you can have snapshot isolation without blocking
> inserts or updates.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

Go to page: [1] [2]

Other Threads