Slow inserts

[lemonrecognize]

Describe the bug Inserting 10k entries takes around 10 seconds

To Reproduce

db.execute(`
  CREATE TABLE IF NOT EXISTS someTable (
    fieldA TEXT,
    fieldB TEXT,
    fieldC TEXT
  );`
)

const statements = []

for (let i = 0; i < 10000; i++) {
  statements.push({
    statement: `INSERT INTO someTable VALUES (?,?,?);`,
    values: [
      'value A',
      'value B',
      'value C',
    ]
  })
}

db.executeSet(statements, true)

Expected behavior

Takes a lot less time to insert data

Smartphone (please complete the following information):

• OS: Android

[jepiqueau]

@lemonrecognize sorry, nothing i can do about this. Have you try to compare the time with the time when you put a db.run inside the loop?

[jepiqueau]

@lemonrecognize i use androidx.sqlite.db

[jepiqueau]

@lemonrecognize nothing from your side so i close the issue

[DGeoWils]

I did notice a significantly slower experience on Android compared to iOS (40sec on Android vs 8sec on iOS) to insert about 10,000 records. I was using the following syntax with prepared statements.

INSERT INTO test_table (remote_id, message) VALUES (1,"msg"); INSERT INTO test_table (remote_id, message) VALUES (2,"msg"); INSERT INTO test_table (remote_id, message) VALUES (3,"msg");

I was able to speed it up to <2sec to insert on Android and iOS by using this syntax instead INSERT INTO test_table (remote_id, message) VALUES (1,"msg"),(2,"msg"),(3,"msg")...;

[jepiqueau]

@DGeoWils thanks for this i will have a look. May be you could make a PR which add a doc in the docs folder like PerformanceTricks which just descibed what you have done so it can benefit the developer's community

[jepiqueau]

@DGeoWils look at the executeSet command it has been done for this

[DGeoWils]

I had tried executeSet, and it was not faster than running the inserts the slow way I described above.

I was calling it in this manner:

executeSet([
  {statement: 'INSERT INTO test_table (remote_id, message) VALUES (?,?);', values: [1, "msg"]},
  {statement: 'INSERT INTO test_table (remote_id, message) VALUES (?,?);', values: [2, "msg"]},
  {statement: 'INSERT INTO test_table (remote_id, message) VALUES (?,?);', values: [3, "msg"]},
  ...
]);

However, I may be missing something in the documentation, or reading the code incorrectly. It seems as though I would need to pass a 2 dimensional array into the values parameter and call execute set like this to accomplish what I was able to do using my fast inserts above

executeSet([{
    statement: 'INSERT INTO test_table (remote_id, message) VALUES (?,?);', 
    values: [
        [1, "msg"],
        [2, "msg"],
        [3, "msg"],
        ...
     ]
}]);

Is this how executeSet should be called?

Edit:

After taking a look at the definition of execute set here (https://github.com/capacitor-community/sqlite/blob/57afbb64a1f50d4591c1f25134ac71d04f0f2bb7/android/src/main/java/com/getcapacitor/community/database/sqlite/SQLite/Database.java#L353), it would appear that I could call executeSet in the second way, but this is not documented sufficiently for when I'm calling it with JS. I'd be happy to take a crack at a PR updating that documentation

[jepiqueau]

@DGeoWils yes it is the way you can call executeSet.in this way you open a transaction do all insert values and close the transaction which is exactelly the same that your proposed way. In the slowest way each time you do an insert for one value you open the transaction run the insert and commit the transaction.

[DGeoWils]

I looked through the java code for executeSet and it would definitely work. However in my use case I'm already looping over a blob of data to pull it into a few separate tables, so passing that into the executeSet function which loops over it a couple more times isn't what I want to do.

Good to have as a tool though

[muuvmuuv]

In our case executeSet is about 30sec slower between version 5.0.5 and 4.8.0-1. I tested two iOS 16 simulator devices next to each other.

I am currently looking if its an issue with our queries or with capacitor 5 promise resolve...

[jepiqueau]

@muuvmuuv keep me a are. The executeSet command has not really change except by adding RETURNING support

[muuvmuuv]

Seems to be #414 where I had set the transaction on executeSet to false… any idea why it is that slow in comparison?

As described in the issue, I run a lot of transactions in a loop. To be specific, sometimes it's 2000 transactions * 14 batches.

[davisCrabbBrandt]

I did notice a significantly slower experience on Android compared to iOS (40sec on Android vs 8sec on iOS) to insert about 10,000 records. I was using the following syntax with prepared statements.

INSERT INTO test_table (remote_id, message) VALUES (1,"msg"); INSERT INTO test_table (remote_id, message) VALUES (2,"msg"); INSERT INTO test_table (remote_id, message) VALUES (3,"msg");

I was able to speed it up to <2sec to insert on Android and iOS by using this syntax instead INSERT INTO test_table (remote_id, message) VALUES (1,"msg"),(2,"msg"),(3,"msg")...;

Thank you @DGeoWils! I was had write times taking anywhere from 60 - 100s on Android drop to 1s with this change Thanks for all your work on this plugin @jepiqueau!