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