Transaction question

Hi

I am processing large amouns of records (100K+) for modifying, adding and
sometimes deleting. To speed up things I use transactions. The record size
of the table being processed is rather unpredictable as my users can add
fields to it.

Now what happens it that at a certain point the transaction gets so big that
the server runs out of physical memory and the memory swapping starts... And
performance drops from say 11Krecords/s to 200 records/s

To avoid this, I perform an "autocommit" (Commit/StartTransaction) say
every 10.000 records.
What I would like much more is to check how much space my transaction is
taking up, and rather use that as a trigger for my autocommit call. So my
'autocommit' routine gets a bit more intelligent than a simple counter...

Any ideas, or maybe there's even a better solution to this?

TIA - Hans

> I am processing large amouns of records (100K+) for modifying, adding
> and sometimes deleting. To speed up things I use transactions. The
> record size of the table being processed is rather unpredictable as
> my users can add fields to it.

The recordsize is never unpredictable at runtime; the datadictionary will
return the answer in TnxFieldsDescriptor.GetRecordLength. You can use that
to calculate the needed amount of space for x records in RAM.


--

Eivind Bakkestuen [NDD]
Newsgroups searchable at www.tamaracka.com

Eivind Bakkestuen [NDD] wrote:

> > I am processing large amouns of records (100K+) for modifying,
> > adding and sometimes deleting. To speed up things I use
> > transactions. The record size of the table being processed is
> > rather unpredictable as my users can add fields to it.
>
> The recordsize is never unpredictable at runtime; the datadictionary
> will return the answer in TnxFieldsDescriptor.GetRecordLength. You
> can use that to calculate the needed amount of space for x records in
> RAM.

Just a thought, would this be an estimate, as does'nt block size,
number of records per block etc come into effect.?

--
Keith[NDX];

I See... I can calculate the transaction size by keeping some kind of
administration of new/modified/deleted records.

My problem is however that it is a rather complex database, and lots of
records in lots of tables are modified during my transaction. This would
make my transaction-administrative routines very complex, and they are
already complex enough - believe me.
So from my point of view, the data that the server manages during a
transaction is just the kind of administration required to check how large
the transaction is actually getting.

So it would be nice to know how large, or for that matter, how much space
(percentage?) the transaction occupies of the server memory to make my
Commit/Starttransaction call to flush the changes.

Another possibility could be to check the 'processing speed' and perform the
Commit/StartTransaction call when performance drops below 10% of the initial
speed...

Any better ideas how to chieve the best 'autocommit' call?

Thanks - Hans

"Eivind Bakkestuen [NDD]" <Ieivind.bakkestuenHATE@nexusdb.comSPAM> schreef
in bericht news:42dc8041@wic040d....
>> I am processing large amouns of records (100K+) for modifying, adding
>> and sometimes deleting. To speed up things I use transactions. The
>> record size of the table being processed is rather unpredictable as
>> my users can add fields to it.
>
> The recordsize is never unpredictable at runtime; the datadictionary will
> return the answer in TnxFieldsDescriptor.GetRecordLength. You can use that
> to calculate the needed amount of space for x records in RAM.
>
>
> --
>
> Eivind Bakkestuen [NDD]
> Newsgroups searchable at www.tamaracka.com
>
>
>