Subject Re: [IBO] Re: Please. A solution with TRANSACTIONS for this problem
Author TeamIBO
> If I use a program named IB_SQL, of your Web I
> can see SweepInterval to 20000, and I Can't change it.

To change it you need to...

1. Make sure you are NOT connected

2. Check the checkbox "Set Sweep Interval"

3. Type 0 (or whatever) into the Sweep Interval edit box (which is now
editable (after checking the checkbox).

4. Connect to the database.

Note that the sweep interval would only make a difference if you are
actually committing after every insert. (Also check ServerAutoCommit
is false). However it may be worth testing just as a double check
that you are not missing something in the app.

I notice on a previous message that you mentioned having an IB_Query
to display the results. I presume (hope) that the query is not being
opened/refreshed until after the inserts are complete - you are not
trying to refresh after every insert are you?

I looked back through this thread but cant see where you said what
server version you are using. There were some issues relating to
ForcedWrites in Firebird 1 RC1 - I doubt that they would have this
impact, but if your database was created under RC1 I would be inclined
to recreate under RC2 (or backup and restore). Just to be sure.

> 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.

I guess it is possible that some of your problems come from disk
thrashing - are you running low on disk space?

If all else fails then I would say that ForcedWrites disabled would
not be a huge issue but it would be better to resolve whatever the
problems are before proceeding.

> 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.

IB/FB perform optimistic processing - all posts are written as though
they will be committed. So if you choose to rollback there is a lot
to undo and so it can take quite a long time to rollback.

In the past I have inserted large numbers of records (500,000+) with
no obvious problems. The only time I had performance problems was
with a large select (containing embedded selects and computations),
and that seemed to blow out the server memory.

Speaking of memory - I dont know what operating system you are using,
but it may help isolate the problem if you can show the memory
consumption of the programs involved (the app itself and ibserver).
Under NT/2k you can do this with TaskManager.

Geoff Worboys - TeamIBO
Telesis Computing