--- In IBObjects@y..., "Arno Garrels" <arno.garrels@g...> wrote:

> Marco,
>
> Thank you very much for taking the time.
>
> > Sorry, but can't be true. If true, al lot of people will have some GB
> > database size.
>
> Hopefully you are right.
> But do they insert/delete records in a 1 min. loop 24/7?
>
>
> > What isolation level do you use? Read committed, I hope.
>
> tiCommitted, CommitAction=caInvalidateCursor.
>

Well, I can't tell you where you are wrong, so here just some
suggestions to improve your code.
One stupid question, but sometime the most obvious things are the one
causing problems, you have linked the queries with a IB_Transaction
compoent, or you are using <default> value in IB_Transaction property?

Since as far as I have understood, your tablea are used to move data,
not data entry with a grid, I can recomand you CommitAction=caClose,
that automatically closes tha dataset when you do a commit in the
transaction.

>
> > And what's the code that performs the delete and what performs the
> > insert?
>
> My first try:
>
> Mastertbl.Close;
> Detailtbl.Close;
> DB.Commit;
>
> DB.StartTransaction;
> Detailtbl.EmptyTable;
> DB.Commit;
>
> DB.StartTransaction;
> Mastertbl.EmptyTable;
> DB.Commit;
>
> Second try:
>
> UnlinkTables;
> DB.StartTransaction;
> Mastertbl.Open;
> while not Mastertbl.Eof do
> Mastertbl.Delete;
> DB.Commit;
>
> DB.StartTransaction;
> Detailtbl.Open;
> while not Detailtbl.Eof do
> Detailtbl.Delete;
> Detailtbl.Close;
> DB.Commit;
> LinkTables;
>

Well, don't to "second try". All the work must be performed at server
level, second try retrieves all the data from the server and deletes
data row by row... too bad.
Never used .EmptyTable, I should check the source to know what it
does. In any case, I would have choose from this two possibilities:
a) use a TIB_DSQL with a "delete from mymastertable". I use
referential integrity constraints when a master/detail relationship is
involved, so deleting the master records produces a "cascade" effect
to the detail ones. Don't know if it's faster than deleting details
and then masters, maybe bot, but it's more apropriate, if performances
is not an issue (Firebird is very fast, test it and decide).
b) create a stored procedure in the server that deletes the tables

>
> Inserts:
>
> Mastertbl is populated from a 'Select Query',
> Query is closed then Commit is called.

I think that the right sequence is Commit and then close, not close
and then commit (Helen, am I wrong?). Anyway, with CommitAction to
caClose, you have the dataset automatically closed (I use explicit
Close even with caClose, just for source clarity).

>
> Mastertbl.First;

The above line starts a transaction! Everything in Firebird is inside
transactions, only closed dataset does not require one.

> DB.StartTransaction;
> ...
> Recursive file search, using FindFirst/FindNext;
>
> Detailtbl.Insert;
> ...
> Detailtbl.FieldByName('PATH').AsString := Zip_Encrypt(FilePath);
> tbl.Post;
>
> Commit is called after ALL inserts for current master record are

done, then

> I go on with next master that starts a new transaction again.

I would have written:

// start phisical transaction, but I'm confused on this argument
if not DB.Started then
DB.Start := True;
Mastertbl.First;

the rest of the code is ok. I assume you can't do this loop inside of
a Stored Procedure because you need Zip_Encrypt(), right?

>
> When filling finished I iterate thru the records, start either FTP

or SMTP

> and send the files. SMTP/FTP results are written to another field

'Error' in

> Detailtbl.

I assume that this takes a long time, and you send it FTP row by row?
In the meanwhile, is some other client starting another "1 minute
loop" against the same tables?

> Those updates are committed after all detail records for a single

master had

> been updated.

Reasonable. Of course, with explicit transaction handle, i.e. start
transaction... iterate/write... commit, close

>
> At last I read field 'Error' in order to write a log file and empty both
> tables.

Mmmm... is it just only client application doing this? What tables are
other client affecting? Reading/writing? Or your is a single user
situation? And what's the 1 minute cycle?


regards
Marco Menardi