Hi,

Is there a maximum limit of the number of records to be inserted in a table as 1 transaction with 1 commit?

Myself I would need to store in one transaction app. 15.000 records. In the end I don't need the commit. I'll use abort.

I need this functionality to build a logic for an array. Accordingly with a logic some records have to be stored in a list. During some later checks they could be taken out this list.

I'm trying to avoid to check every time each element of the array and then move the array to left if an element was taken out.

Is there any other logic than using a temp table to do db.insert for each record to be stored and db.delete for each record to be taken out from the list?

Thanks and regards.

Do complete your user profile with regards to the Baan software version, Database software and OS version. This will help other members when diagnosing your problem.

http://www.baanboard.com/baanboard/usercp.php?s=

Is there a maximum limit of the number of records to be inserted in a table as 1 transaction with 1 commit?
Here is some information -

BaanERP Application Software Engineering Guides

Size of database transactions
--------------------------------------------------------------------------------
Problem
For optimal performance, database transactions must not be too small and not too large. However they must always be logical transactions.

Large transactions will also have relatively long locking times. Depending on the frequency of updates done by other users, this can cause locking problems.

Other problems can also occur, for example, run-time tools (bshell, driver, and so on) consume more memory and CPU, more rollback segments are needed in the RDBMS.

Solution
If possible, do not implement the commit.transaction()for each single update. On the other hand, if possible, do not implement the commit.transaction() for too many updates.

It is difficult to give hard numbers for this. The optimal transaction size depends on many factors.
Currently a general rule can be about 100 to 250 updates per commit. (The commit rate). Apply these numbers only if the chances are small that other users update the same records.

Example
Bad situation: one commit per updatedb.retry.point()

select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
commit.transaction()
endselect
Bad situation: commit for (more than) 1000 updates

db.retry.point()
select table.*
from table for update
selectdo |* table can have more than
... |* 1000 rows !
db.update(table, db.retry)
endselect
commit.transaction()
Improved: commit per 100 updates

long number.of.updates

db.retry.point()
number.of.updates = 0
select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
number.of.updates = number.of.updates + 1
if number.of.updates = 100 then
commit.transaction()
number.of.updates = 0
endif
selecteos
commit.transaction()
endselect
The selecteos section with the commit.transaction() is needed. It will be explained in help page 'implementation of selecteos'.

> I'm trying to avoid to check every time each
> element of the array and then move the array
> to left if an element was taken out.

If you have to define a new table anyway then
you might just as well commit the records and
execute deletes instead of an abort.transaction.
You can add the process-Id (pid) to the table
index to keep the 'arrays' of different users
seprated.

If you know that the maximum number of entries is
limited I would consider staying with arrays.
Possible suggestions:
- keep a special array to keep track of removed
entries and do not move the array
- make entries empty in stead of removing them

Best regards,
Marc

You can add the process-Id (pid) to the table index to keep the 'arrays' of different users separated
Don't use pid, because this is the internal process id within the bshell. It is not unique for each user. Use bshell.pid() instead of this.

I need this functionality to build a logic for an array. Accordingly with a logic some records have to be stored in a list. During some later checks they could be taken out this list.
You can consider to use qss.search() and qss.sort() options.

Searching and sorting data overview and synopsis (http://www.baanboard.com/programmers_manual_baanerp_help_functions_searching_sorting_data_overview_and_synopsis)

sorting sample programs (http://www.baanboard.com/programmers_manual_baanerp_help_functions_searching_sorting_data_example)

qss.search() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_searching_sorting_data_qss_search)

qss.sort() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_searching_sorting_data_qss_sort)

Please find below my comments.

Replys for NPRao's Posts:
-Thanks a lot for your answer
- sorry about my profile. Since I'm working in environments for 4b2 to 5c I didn't know which one to put. I'll specify the details in posts
- thanks for partial docu. I'm quite familiar with it
- I know also the qss functions. As I already specified the performance is a big issue in this program (actually I'm optimizing an already existing script). I had the feeling that using db.insert for array elements that I need to store and db.delete for the ones I want out it will be the faster way to do (I get the sort and the search in one step).

Do you think that the performance given by qss.search(), store or take out element from array and qss.sort() can be compared with db.insert as performace? Please let me know your opinion.

Replys for Marc van Kessel:
-Thanks a lot for your answer
- I'm trying to avoid commit.transaction() for performance reasons. I have to fill, sort and do a query for this array (that can have sometimes up to 15.000 elements) in less than 2sec.
-The process ID is indeed unique only for one bshell.
- Set on empty the array elements that i want out : this is what I'm doing for the moment but it's eating performane since my array gets sometimes to have 200 elements and 198 are empty.

Replys for Jaap JD:
-Thanks a lot for your answer



Looking forward to your posts.

Do you think that the performance given by qss.search(), store or take out element from array and qss.sort() can be compared with db.insert as performace? Please let me know your opinion.
You have to test it and validate which one is better design using - mtime() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_timers_mtime)
If you are using array you are limited by the size -
This allocates memory space to the specified variable at run time (up to a maximum of 5Mb). The variable can be a string or an array (of any type).
But if you are using a table then you are not limited by the number of records. But you should come up with good design for the fields and indexes. Also depends on if its a common/shared company or a company specific table, tracking by user id or process run.
- Set on empty the array elements that i want out : this is what I'm doing for the moment but it's eating performane since my array gets sometimes to have 200 elements and 198 are empty.
You can always keep a track of the array size and resize it up or down.
I want out it will be the faster way to do (I get the sort and the search in one step).
Alternatively you can put all the values into a file and you can use the BaaN or Unix sort-

[DEV:bsp]/app/lms/bin>sort6.2 -?
Usage: sort [-qe error-file] [-funbirdcmt'x'] [+beg_pos[-fnbird]] [-end_pos]] [-{o|qo} outfile] [file] ...
To find the pattern in the file, you can use the function in the library - ottdllfilehand
pattern.in.file
long pattern.in.file( const string file(), const string pattern() )
pre: true
desc: 1: The pattern is in the file
0: The pattern is not in the file.
< 0: An error occurred. Probably the file cannot be read.

Be aware that is not a perfect pattern match, but a partial match.

Good Luck... :p