Batch Insert and memory usage

[kneighbour]

I am using the latest UniDac and XE7 with Firebird 3. I am inserting 335K records into a particular table. These records are fairly large and I am finding that I can only insert about 200 or so at a time. If I try to set 1000 I get an error. I have tried to trap the error but to no avail. I think it has to do with running out of memory. In any case, if I drop the batch size down to 200 or so, it all works.

what happens is that the line Query1.Execute(cnt); fails and the system leaves the procedure. Standard exception behaviour except I do not seem to be able to trap it.

I am thinking that a large batch size is better than a small one. Correct? I am also worried that this 200 limit is on my development system - there is no way of knowing if this limit would pertain to a working customer's machine or not. It is a bit of a concern. It would be nice if the Batch Insert errored out so that I could trap it and allow the user to do something about it. A problem in the script will be picked up by the error trapping, but not this out of memory problem.

I am basically using the code as supplied in the documentation

cnt=200;
Query1.SQL.add('INSERT INTO tablename');
Query1.SQL.add(' (fieldname1, fieldname2, etc...)');
Query1.SQL.add('VALUES (');
Query1.sql.Add(':fieldname1,');
Query1.sql.Add(':fieldname2'); etc
Query1.SQL.add(')');

Query1.Params[0].DataType := ftFloat;
Query1.Params[1].DataType := ftFloat; etc
Query1.Params.ValueCount := cnt; // specify the array dimension:
// populate the array with parameter values:
for i := 0 to cnt - 1 do
begin
Query1.Params[0].AsFloat := some value;
Query1.Params[1].AsFloat := some value;
end;
try
Query1.Execute(cnt);
except
on e:exception do ShowMessage(e.message);
end;

Any thoughts?

[ViktorV]

IBDAC has no limitations on memory usage. Therefore, the only limitation of memory usage is the limitation imposed by the operating system.
The memory consumption when using batches depends on the value of the ValueCount parameter and does not depend on the number of values ​​passed in Execute. The memory usage will be the same after executing the code

Code: Select all

UniQuery.Execute (50000);

and

Code: Select all

for i: = 0 to 4 do
  UniQuery.Execute (10000, i * 10000);

Optimal values ​​of the ValueCount parameter value are selected independently in an empirical way. You can use the specified algorithm to solve the issue.
Note, after executing the Execute method, memory cannot be automatically released, because neither the SQL statement nor Params.ValueCount are changed. Executing Params.Clear or SQL.Text: = '' causes the parameter to be deleted and the memory to be released.
Also, you can explicitly set the size of the string parameters in your application to increase performance and .

[kneighbour]

Thanks for the feedback. You say "You can use the specified algorithm to solve the issue." - what specified algorithm is this?

I have spent a few days looking at both Batch and Loader functions to do the same inserts to the same table. From this experience it seems that the Batch mode is a lot faster, but suffers terribly from memory overloading. I am basically inserting records in batches of 200 as that is about all it can do with my setup. The Loader does not suffer any memory problems but is a bit slower. I can also use much larger batches, and 1000 seems quite ok.

[FredS]

Batch mode is a lot faster, but suffers terribly from memory overloading. I am basically inserting records in batches of 200 as that is about all it can do.

I've been looking for my notes on this but can't find them right now.
So from memory; Batch/Bulk updates fall under memory restrictions by FB3 that has to do with Page Size, data size and the type of Insert SQL Used.

I believe my answer came from here a couple of years ago and ended up having to re-code for smaller batches: https://www.mail-archive.com/firebird-s ... roups.com/

[ViktorV]

The memory consumption when using batches depends on the functionality specificity and limitations of Firebird: for example, restrictions of SQL statements size, restrictions of using different data types, etc. IBDAC tries to form queries considering many limitations of Firebird, but, unfortunately, we cannot fully automate this behavior. The optimal values ​​of the ValueCount parameter depend on very many environmental factors of each particular case, therefore the most optimal values ​​for the ValueCount parameter value are selected independently in an empirical way.
As we have already mentioned, in order to reduce memory consumption when using batches, you should choose the optimal ValueCount parameter value and, if possible, set the size for string parameters in your application by yourself.