I have an abstract question about SQL Anywhere speed limitations when performing many insert/update statements concurrently by different connections. There is a procedure P, which performs many updates of existing records of various tables and also inserts new data. This procedure processes some piece (interval) of data. We launch that procedure for N different (not intersecting) data intervals concurrently (in separate dbisql instances) to achieve more overall processed data amount per minute.
While N is 7-10 we see almost proportional increase of processed data amount, i. e. processed data amount is almost N-times larger than running only one instance of procedure P. Each instance uses almost full logical CPU core (max_query_tasks is set to 1 because when set to 0 or another value the speed decreases drastically in this case).
When N reaches or exceeds 7-10 instances we see that processed data amount per minute no longer increases. It stays the same, for example, when we increase N to 17 instances. Each instance processes less data per minute and each logical CPU usage drops noticeably. Free RAM for cache is enough. Customer reports that disk usage is not very high as well.
What can be the reasons for such a limitation? Are checkpoints the only possible cause or is there something that we can configure, try, etc.? Thanks.