# Bulk Insert Optimization in C++

> The crux of the "bulk insert" optimization is in the ability to group multiple "insert" and "insert or update" statements into single a RSQL transmission, thus saving on round trips. The optimization allows passing an array of insert statement parameters into the a single `executeStatement("INSERT INTO ...")` call.

> Suppose the application needs to add ten objects into the database. The straight-forward way of doing that is illustrated by the following pseudo-code fragment:

>         for (int i  = 0; i < 10; i++)
>             {
>                 // Set up the statement's parameters
>                 int4 key = i;
>                 int4 value = key + 10;

>                 // execute
>                 engine->executeStatement("insert into Record (id, value) values(%i %i)", key, value);
>             }

> The "bulk insert" does the same with a single "execute". The parameters for multiple insert operators are "packed" and then passed into the `execute()` function:

>         // Create parameters’ arrays

>         Array* key_array = Array::create(engine->getAllocator(), tpInt4, 0, 10);
>         Array* value_array = Array::create(engine->getAllocator(), tpInt4, 0, 10);
>         for (int i  = 0; i < 10; i++)
>         {
>             // Set up the statement's parameters
>             int4 key = i;
>             int4 value = key + 10;

>             // writes parameters of each statement into arrays
>             key_array->setAt(array_idx, IntValue::create(engine->getAllocator(), key));
>             value_array->setAt(array_idx, IntValue::create(engine->getAllocator(), value));
>         }

>         // Call the 'execute' once. Internally the  execute 'insert into Record' 10 times using parameter from the key_array and value_array.
>         engine->executeStatement("insert into Record (id, value) values(%v, %v)", key_array, value_array);

>         // Free memory allocated by the arrays and their content
>         DELETE_OBJ(engine->getAllocator(), key_array);
>         DELETE_OBJ(engine->getAllocator(), value_array);

> The bulk insert works locally or over the remote SQL protocol. Performance improvements over in the later case can be very significant.

> Furthermore in addition to the `INSERT` the bulk insert optimization can be used in the `INSERT OR UPDATE` operator (see this and related documentation articles). For example:

>         Array* key_array = Array::create(engine->getAllocator(), tpInt4, 0, 10);
>         Array* value_array = Array::create(engine->getAllocator(), tpInt4, 0, 10);
>         for (int i  = 0; i < 10; i++)
>         {
>             int4 key = i;
>             int4 value = key + 10;

>             key_array->setAt(array_idx, IntValue::create(engine->getAllocator(), key));
>             value_array->setAt(array_idx, IntValue::create(engine->getAllocator(), value));
>         }

>         engine->executeStatement("insert or update into Record (id, value) values(%v, %v)", key_array, value_array);

>         DELETE_OBJ(engine->getAllocator(), key_array);
>         DELETE_OBJ(engine->getAllocator(), value_array);

> Note that the `INSERT OR UPDATE` requires a unique index over the table being modified and allows updating objects when the key matches the new value. The SDK includes a sample that demonstrate the use of the "bulk insert” and illustrates the said performance improvements. See samples/native/sql/api/sql-14-perf.

Related Topics