I did more tests...
After inserting that record about 350,000 times, status 2015 (insufficient
memory) is returned. So there is a limit, but it's not clear to me what the
limit is precisely.
Closing some apps to free memory doesn't help to continue inserting records.

Can someone from Pervasive clarify please ?

Serge

"S Nelissen" <spam (AT) spam (DOT) com> wrote

The size of a transaction *IS* limited to the cache size, and you will get a
Btrieve Status 100 returned if you exceed this.

However, your test is flawed -- your updating a SINGLE record will only change a
few pages in cache, and will not overflow it.

To better test this -- open a file that has a size (on disk) of about 100MB.
Start a transaction and start updating records (you don't necessarily need to
actually change any data). When you have changed a large part of the file and
exceeded the maximum dirty pages in cache, the transaction will fail and you'll
get the Status 100 back.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: September 16-18: See our web site for details!

S Nelissen wrote:

Hi Bill.

Have you read my other post ? I cannot believe that 350,000 inserts fit
into 32 MB cache, so the limit must be imposed by something else than the
cache size. And, status 2015 is returned, not 100. But, it isn't an
ordinary status, but a MKDE-status and it is logged in Pvsw.log as :
"07-11-2003 14:32:59 W3MKDE 000000B6 W3dbsmgr.exe WRKSNE1
E MicroKernel cannot get sufficient memory to
complete the operation."

I think we both need some explanation from Pervaisve :-)

Thanks for answering,

Serge

PS Tested with V7


"Bill Bach" <bbach (AT) cncdsl (DOT) com> wrote

The actual answer is "it depends on which version you use".
There was an internal change in behavior handling tansactions with
Pervasive.SQL V8.
Prior to Pervasive.SQL V8, yes the entire transaction has to fit in
cache pages. Pervasive.SQL V8 moves some of the data structures
outside the cache into system memory, but all the data pages still
have to fit in L1.
There is a side effect.
Huge transactions displace cached data pages from the cache.
This can adversely affect performance.

Yes all the updates from the transaction have to fit in Cache or you
will get a status 100. Perhaps you may get an error allocating the
aditional memory to handle it.

My question is, why is such a huge transaction being done.
Transactions were designed to provide atomic updates across multiple
files. They were not really designed to hold pending batch
operations. The entire pending transaction must fit in memory (part
of that "pending transactions not affecting the data files").

The simple answer is:
It was not designed to do what you are trying to do.

Is the use of a single transaction required?
Can it be broken up into smaller chunks?

Keep in mind that no matter what you test for some user will go +1 on
the size of the data set and -1 on the setting to handle it.

Leonard

On Mon, 14 Jul 2003 10:01:50 +0200, "S Nelissen" <spam (AT) spam (DOT) com>
wrote:

Thanks Leonard,

We are still using V7.



engine. Isn't it ?

that import errors can occur (wrong data, duplicate errors, syntax errors in
the text files, etc...) Since much of the data is related to previous
inserted data, we have to abort the import routine. Then, the user has to
take several steps...
1. Sometimes, there is wrong data in the import file. So he has to correct
the import file.
2. But, most of the times, the data in the imort file refers to lookup
tables in our app that are incomplete (missing reference). The user has to
complete the lookup tables in our app.
3. This is the most annoying step, the user has to restart the import, but
from the point where the error occured.

This method also forces us to abort the import when the first error occurs !
A much better way is to continue importing the text file until the end and
report all errors in 1 log file. But therefore we need to start a huge
transaction and do a roll back if an error occured. The user has a list of
_all_ errors at once and he doesn't need to know the exact line where the
import has to restart after an error (which is not that simple because a
'document' consists of many hierarchical organised lines - a bit like XML
but without tags).

Normally, the imports are not that huge (maybe several thousand records, but
not 100,000's). I only tried until I received a status code, to know what
to expect to be the limit for transactions. It seems to be enough.


split his import files into smaller parts. But, it's not clear what status
to expect. You say 100, I get 2015. Why ? And, how can a transaction be
limited by the cache if 350,000 pages of 4K seem to fit in a 32 Mb cache
(and the transacion log files exceed 1 GB in size). These pages are not
compressed in L2 cache because I still use V7 in our app.


Kind regards,

Serge


"LHarvey" <lharvey (AT) austin (DOT) rr.com> wrote

I think this is a _very_ valuable idea. But, I see some
problems/difficulties...

1. If the holding table is a Btrieve file, we need a special editor to make
corrections in the Btrive file
2. We import documents like invoices etc... Most of the time, they are
numbered automatically. By skipping some (faulty) documents, we create
documents whose document numbers are out of order.
3. Sometimes data in one record is dependent of the previously imported
record. Skipping records might cause problems.
4. Some users want it to be all or nothing

So, I prefer using one big transation, and I can live with a limitation
about the size of it. I just need to know _exactly_ what the limitation is
and why status 2015 is returned while status 100 is expected.

But, your idea gives me inspiration for another method...
Is there a method of _canceling_ a continous operation ? I could put the
files in continous operation with STARTBU, do the import and end it with
ENDBU if everything is OK. I could do a 'CANCELBU' (invented command) if
something went wrong. Any idea if this could be possible ? Might be a nice
feature request :-)

Thanks a lot,

Serge

"Guy Dawson" <gnues (AT) crossflight (DOT) co.uk> wrote

S Nelissen wrote:

Indeed.

So you're allocating a reference number for each invoice. Is the order
in which they are loaded that important? Do they arrive at the import
system in a particular order that must be maintained? Could you pre
allocate numbers in the holding table and use those on the actual
import?

Within a particular invoice? The import into the main system might have
to use a transaction as the invoice level to either import the whole
invoice or none of it.

Users! Ho hum. I'd hope a new system would have advantages that could
be used to encourage them to change their minds.

I think the problem is that the limit is a system limit and dependant on
memory usage over the system as a whole. If you add more RAM can you do
bigger transactions?

I don't know of any way to cancel continous operation mode. Within the context
of its intended use (backups) it does not make sense.

Guy
-- --------------------------------------------------------------------
Guy Dawson I.T. Manager Crossflight Ltd
gnues (AT) crossflight (DOT) co.uk

This is the answer I received from Kevin Lewis (an expert Pervasive
engineer)...


Limitations of transactions (how big can it be ?)

That depends on which engine you are using. V8 uses memory a lot more
efficiently than V7.94 and does not have the limitations inherent in v7.94.

All engines since version 6 have been able to take advantage of shadow
paging, which allow a dirty page to be written out to disk even though it
has not yet been committed. Since the pending pages do not need to remain
in cache for the entire transaction, the stress on the system comes from
cache headers. These are small pieces of memory that are linked up many
different ways and they track logical page numbers and physical page
numbers. If a transaction affects 100 pages, then there will be

100 logical pre-image page numbers

100 logical shadow pages numbers,

100 physical pre-image page numbers, and

100 physical shadow page numbers.

These 400 cache headers must remain linked up in memory until the
transaction is committed, whether the associated page buffer is in memory or
not. There has been an evolution in recent releases with how these cache
headers are stored and how big they are.

P.SQL 2000 SP2, SP3 & SP4 - Cache headers were moved out of the
main page buffer cache into a dynamic memory pool that would grow but not
shrink. This pool had a maximum upper bound which was about 80% of the
cache size. Also, the cache headers increased in size from 32 bytes before
SP2 to about 64 bytes each. This limitation, *80% of cache size to store
cache headers, affects how big the transaction can get.

V8 - Cache headers come from a dynamic pool that can shrink and
expand. They are now limited by the memory available to the engine. So
with V8, you can effectively do much larger transactions. Keep in mind
however that all pending pages are written to shadow locations, which
increase the size of the file.

Status 2015 is returned instead of status 100. Status 2015 is a memory
error that is reported to the PVSW.LOG whenever a status 101 (No OS Memory
Available) is returned to the application thru Btrieve. This is the error
that is returned by the v7.94 engine when the cache header pool is maxed
out.

Kevin

---------------------

"S Nelissen" <spam (AT) spam (DOT) com> wrote