Use of prepared statements in batch operations

[EduFrazao]

Modified batch executors (ExecuteSqlBatch) to reuse prepared statements. From the begining of the API, SQL Batches supports SQL parameters as an Array of objects, or a array of arrays. So, you can use the API like:

const sql = 'insert into some_table (col1, col2) values (?, ?)'
const params1 = [1, 2];
const params2 = [1, 2];

db.executeBatch([sql, params1]);
db.executeBatch([sql, params2]);

Or, you can dispatch a single time:

const sql = 'insert into some_table (col1, col2) values (?, ?)'
const params1 = [1, 2];
const params2 = [1, 2];
const params = [params1, params2]

db.executeBatch([sql, params]);

When running in the second form, the driver will reuse prepared statments to reduce the overhead of preparing the same query multiples times. The statments will be reused and rebinded with new parameters.

Also, new overloaded methods were added to the bridge to check and collect database instance pointers to use this reference instead of database name in some operations, avoiding multiple database checks and map access in batch operations.

[ospfranco]

This adds too much complexity I don't really feel like maintaining long term. I believe the batch operation is already fast enough as it is. I will leave it open for now if enough people show up and ask for it.

[EduFrazao]

This adds too much complexity I don't really feel like maintaining long term. I believe the batch operation is already fast enough as it is. I will leave it open for now if enough people show up and ask for it.

Hi @ospfranco! Sure, I understand.

I've made this change because we have some batch operations here that performs more than 100k insert/updates (And for this ones, I can't convert them on SQL exports to load with file loads). In some old devices, this can take more than 20 seconds. With this patch (in my use case) it shows between 15% and 20% of gains.

File loads still the fastest form of bulk load of this library in my apps. We have some cases where wee need to load more than 800k rows. In some devices, this can be done in less than 5 seconds :).

[mjmasn]

Just testing this out... inserting 300k records in batches of 100k with executeBatchAsync (ran out of memory trying to insert 300k at once on the Pixel 6 Pro).

On iOS simulator each batch goes from 25s down to 11s On Pixel 6 Pro it's 60s down to 20s

That's pretty incredible. Totally understand maintenance concerns, will patch-package it for now and see how we get on.

[EduFrazao]

Just testing this out... inserting 300k records in batches of 100k with executeBatchAsync (ran out of memory trying to insert 300k at once on the Pixel 6 Pro).

On iOS simulator each batch goes from 25s down to 11s On Pixel 6 Pro it's 60s down to 20s

That's pretty incredible. Totally understand maintenance concerns, will patch-package it for now and see how we get on.

Hi @mjmasn For massive loads there is pretty good performance boost. I'm trying to figure a way to use this method with SQL Dump files via loadFile.

[EduFrazao]

Just testing this out... inserting 300k records in batches of 100k with executeBatchAsync (ran out of memory trying to insert 300k at once on the Pixel 6 Pro).

On iOS simulator each batch goes from 25s down to 11s On Pixel 6 Pro it's 60s down to 20s

That's pretty incredible. Totally understand maintenance concerns, will patch-package it for now and see how we get on.

Hi @mjmasn. Are you still using this patch? I'm running it in production here already. Did you find any problems?

[ospfranco]

Completely re-wrote the implementation to a host object. Going to close this as it is outdated and I won't maintain it in the future. Thanks anyways @EduFrazao !