Hello:

I have proved your version of the stored procedure (using IF EXISTS),
and in most of cases it's better than my solution (based in a FAQ
questions). I'll use your version. Also I was proved another
version of your idea using RDB$DB_KEY but it's more slow, the way I
prove it.

But the problem with the continuous write is permanent. I prove that
en TIB_TRANSACTION Autocommit is False, and in IB_CONNECTION
SweepInterval is 0. If I use a program named IB_SQL, of your Web I
can see SweepInterval to 20000, and I Can't change it.

I think I'm not comitting every insert. I have the next source code:

==================================================
if not IB_transaction1.InTransaction then
IB_transaction1.starttransaction;
---
--- bucle of insertions (executions of the stored procedure.. DSQL)
---

And then, the user selects between two options:


1) if IB_transaction1.InTransaction then
IB_transaction1.CommitRetaining;

2) if IB_transaction1.InTransaction then
IB_transaction1.RollbackRetaining;
==================================================

The only way to avoid this continous write in disk is with FORCED
WRITES disabled. I know that it's not recommendable but it's exactly
what I like, and I don't know if do it. My program is only for 1
user.

The problem when I have 100.000 inserts and then ROLLBACKRETAINING,
with the resulting DataBase with 8MB or more is present, but I think
this is not important (relative), if is possible avoid the continuous
writes in disk.

Thanks,

--- In IBObjects@y..., Geoff Worboys (TeamIBO) <geoff@t...> wrote:
> > Problems with the sweep interval????
>
> Make sure you are not committing with every insert - as in such
cases
> the sweep interval could be causing your problems (defaults to kick
in
> every 20000 transactions).
>
> Ensure you are using an explicit transaction with AutoCommit set
> to false.
>
> Also, I see your procedure looks like...
>
> > CREATE PROCEDURE INS_UPD (PID VARCHAR(6),
> > PNAME VARCHAR(50))
> > AS
> > begin insert into test(id,name,numb) values ( :pId, :pName, 1);
> > when sqlcode -803 do
> > update test
> > set numb=numb+1
> > where id=:pId;
>
> Note that attempting an insert and then catching the failure may be
> inappropriate in your situation. You may find you get better
> performance by explicitly doing what you expect the above code to do
> implicitly...
>
> IF (EXISTS(SELECT ID FROM TEST WHERE ID=:pId)) THEN
> <update>
> ELSE
> <insert>
>
> I am not sure if this will make any difference or not, it depends on
> the internals of the engine.
>
> --
> Geoff Worboys - TeamIBO
> Telesis Computing