batch updates, total and partial

[albourgz]

Hi,
There is a behaviour I don't understand using c++ builder 10.3.2/ unidac 8.3.2 / 32 bits vcl. (issue is the same with unidac 8.0.1), using batch updates.
I have to insert lots of rows in a table, don't know how much, so decided to insert by batches of 100. Query is a simple "insert into table values (:1,;2)"
So I set q->Params->ValueCount=100.
Each time I have 100 records, I run

Code: Select all

// in insert procedure
q->Params->Items[0]->Values[i]->AsInteger=p1;
q->Params->Items[1]->Values[i]->AsString=p2;
if (i==100) {
            q->Execute();
            q->Params->ValueCount=100;
            i=0;
            }

and restart populating the array
And the last time (<100 values populated) I run

Code: Select all

if (i>0) {
            q->Execute(i);
            }

Problems:
* it seems Execute() doesn't insert 100 values although 100 parameter values are given. Is it right?
* to avoid the issue I tried to set ValueCount to 100000. I got an out of memory exception at execute(i);
The computer has 16GB ram, but it is 32 bits. Even if the app receives 3Gb without swap, 3GB/100000, it means 30KB per row, which seems to be a lot to me. Is it normal that it crashes?

Thanks.

[evgeniym]

Hi there!
Thank you for contacting Devart and for your inquiry!
When using Batch operations, the number of operations to be performed is always indicated, that is, the call to Execute looks like:
q->Execute(i); или q->Execute(i, j);

Your piece of code should look like this:

Code: Select all

// in insert procedure  
q->Params->Items[0]->Values[i - 1]->AsInteger=p1;  
q->Params->Items[1]->Values[i - 1]->AsString=p2;  
if (i==100) {  
  q->Execute(i);  
  q->Params->ValueCount=100;    
  i=1; 
  }  

How to use Batch operations is described in more details in this article:
https://blog.devart.com/using-batch-ope ... nents.html
Please note that when working with a large set of data, you need to pay attention to the size of the saved data. You are using the String data type, please note how much memory each line takes.
Without the additional settings, 32-bit processes have access to 2Gb, but not 3Gb of RAM.
Please, feel free to ask in case of any further questions!

Regards,
Evgeniy

[albourgz]

"Without the additional settings, 32-bit processes have access to 2Gb, but not 3Gb of RAM."
1. Which additional settings??????
2. 100000 strings on 1Gb would make 10Kb/String. Does the UnicodeString "aaaaaa" use 10Kb?

[evgeniym]

Hi!
Thank you for your reply!

1) You can find out more about memory limitations by the link
https://docs.microsoft.com/en-us/window ... s-releases

2) Please note that we cannot determine what exactly caused the error without the sample application.

Regards,
Evgeniy