At 04:01 PM 22/06/2003 +0700, you wrote:

>Hi Helen,
>
> > Unfortunately, you are looking for a file-based database to do
> > what you want, and a transaction-driven, multi-generational
> > database is not designed to do it.
>
>Hmmm.. how if I compare it with another RDBMS, say DB2. AFAIK, please
>CMIIW, DB2 also able to do what Firebird can do without growing the
>database file size.

I truly doubt that the steps you describe below work with DB2 either. DB2,
like Firebird, is a transaction-driven RDBMS.

>What is muti-generational database?

The term I used was "multi-generational architecture". This means that,
for each transaction accessing the same row in the same table with an
update pending, the RDBMS holds one new record version. The DBMS keeps
details of which transaction first attempted to update the row and uses
this information, together with the transaction isolations of each
competing transaction, when deciding which transaction has the "right" to
commit its own new version. For more detailed information, there is a
paper at www.ibphoenix.com about the multi-generational model.

>Why Firebird is designed using it?

Because the Firebird family of RDBMSs was designed from the beginning to
provide the most optimistic concurrency environment possible for multiple
users. It enables a writer to work with a row (update it, delete it)
without blocking readers from seeing it (rather, a version of
it). File-based databases that do what you think Firebird *should* do
can't handle concurrency, so they place physical locks on records - even
tables in some cases - and everyone else has to wait until it's released.

> > That said, the times and sizes you report at least show that
> > something is terribly wrong with what you are doing. If you
> > can get past the need you perceive to not hold back-versions
> > (which Firebird does to protect your data in a multi-user
> > environment) we can give some guidance on how to prevent your
> > database from growing so much and from degrading in
> > performance.
>
>Alright, maybe I did something wrong with my application. FYI, I'm
>building a database application with Kylix 3. I'm using dbExpress
>component (TSQLConnection and TSQLQuery, for precisely) to access the
>database. In pseudo-code, this is what my application do:
>
>---
> 1: open database connection;
> 2: for i = 1 to some_limit do
> 3: for j = 1 to some_limit do
> 4: begin
> 5: update one_table set one_field = a_value;
> 6: select some_records from some_fields
> where match_conditions;
>
> 7: do something; // no database access here.
>
> 8: for k = 0 to result_set_record_count - 1 do
> 9: begin
>10: update one_table set one_field = a_value
> where match_conditions;
>11: commit retain;
>12: end;
>13: end;
>14: close database connection;
>---

I have absolutely NO idea what this pseudocode is trying to do but, if it's
supposed to represent something you are doing with DbXpress, then it's
totally bizarre.

This isn't a Delphi forum, but I suggest you get into the demos directory
of Kylix and load up the demos from the db folder to see how it's done.

> > 6 mins for 50,000 updates is ludicrous and for it to keep
> > getting worse means your client environment is killing the
> > garbage collection.
>
>I'm definitely agree with you, 6 mins for 50,000 updates is ludicrous.
>But, that's what really happening to my database and application.

Hmm, yes, well, if you are using Commit with retain for batch updates and
you are trying to do all this stuff by stepping through a unidirectional
cursor on the client side, then I am totally unsurprised that it takes 6
mins on a good day.

> >From Paul Beach, I got this trick. He said this command can minimize the
>effect of garbage collection. Here is the command: SET TRANSACTION READ
>COMMITTED NO RECORD_VERSION. What do you think about it?

It's the standard transaction setting for interactive work. But it won't
help unless your application is ensuring that the transactions are being
cleared for garbage collection in a timely fashion. Your client app has
really weird flow as far as I can tell and, if you never call a hard Commit
then there is absolutely NO garbage collection happening. (Quite apart
from those loops!!! Yikes!)

What you think of as "tables" are not stored that way physically in the
database. All data get stored on "pages" as streams of compressed
data. Depending on the page_size of your database, one or many rows from a
particular table will be stored on a page. So updating a row isn't a
question of just popping in there and changing a few bytes in a physical
row. What happens is that each insert and each successful uncommitted
update request causes a new recversion to be written on a page, into spare
space if any is found, otherwise, onto a new page.

If it gets committed, then the old page (the delta) is made obsolete and
waits there for garbage collection. As long as there are hard commits
approximately every 10,000 rows, the obsolete recversions will get cleaned
out by garbage collection, and their space becomes available for another
row structure (update or insert) belonging to that table.

No hard commits means that those obsolete versions just sit tight and the
engine has no option but to make new pages to accommodate more and more
recversions as they get added.

The "retain" in "Commit with retain" causes the same transaction context to
be kept open and, as long as the context survives, all of the obsolete
record versions stay in the database. So, for every row your app updates,
there is one recversion that your application is causing to stay glued in
the db file. Your app is making the engine keep on allocating more space
for new pages, instead of letting it re-use the space left after obsolete
rows are cleaned out.

Multiply that by the number of instances of your program that is running
around the network and you'll start to see what's causing all that growth
and - of course - the ever-decreasing performance.

>I need help to overcome this problem, as newbie in Firebird, I can't
>figure it out myself. For any kind of respons and helps, I thank you in
>advance.

Newbie in Firebird is one thing. There's plenty of help here for Firebird
issues. You've got some learning to do to understand transactions and what
a client does when it converses with the database server. The IB Objects
site (www.ibobjects.com) has quite a number of tech info sheets about
client/server and transactions. Although they are not about DbXpress, they
do contain a lot of background stuff which will help you to understand how
Delphi and Kylix (in a very generic and transaction-unaware fashion)
implement the client/server model.

Martijn Tonies has made a DbXpress driver for Firebird. You might like to
go to his site (www.upscene.com) and download the Kylix trial version.

It seems that "newbie in Firebird" isn't your biggest problem for now. I
think you first need to get to grips with how client/server works and then
how DbXpress works. Unfortunately the Kylix help doesn't (help) much. The
demos are slightly useful, in that they do demonstrate how a data access
object does its stuff, although they display a total lack of understanding
of how database applications are implemented at the coal-face.

Another place to go for papers and solutions (of sorts) is the developer
area at the Borland website. There is much more Delphi stuff than Kylix
(this is virtually non-existent) but what goes for Delphi largely goes for
Kylix as well.

heLen