Performance Issue in 3.45+ (due to generatedKeys)

[yuvalp-k2view]

In an INSERT only benchmark performance has dropped by 20-40%

To Reproduce
Insert a few thousand rows in a transaction.
What we are seeing in profiling is calls to generatedKeys, even though we are not accessing the generated keys.
We assume that this happens regardless if user invokes the call and believe this should be done "lazily" so as not to affect performance.

Expected behavior
No performance degradation

Environment (please complete the following information):
Mac M1

[gotson]

Performance is most likely back to what it was before the feature was removed.

Anyhow there's not much way to go around this, SQLite need to get the generated ID straight after the records are inserted, while JDBC allows you to retrieve the generated keys at a deferred time.

[yuvalp-k2view]

20-40 percent is quite huge so I think it's worth some consideration.
A few suggestions:

1. Simplest - perhaps a way to turn it off using a private SqliteConnection API for anyone not intending to read ids.
2. A bit vague but makes sense - Use the executeBatch API as a way to signal that we are in high performance and there is no need to read the row id.
3. Complex - Differed time... what actions is permitted in jdbc between INSERT and read the generated key that are not allowed by sqlite? Perhaps only those trigger the read rowid but doing another INSERT does not. This way if all I'm doing is calling INSERTs, I do not pay the ~30% price.

Wdyt? We'd be happy to contribute a solution

[gotson]

20-40 percent is quite huge so I think it's worth some consideration.

can you maybe share details on how you measured that, and against which version ? Trying the previous version that had support for it would also be insightful.

[gotson]

• A bit vague but makes sense - Use the executeBatch API as a way to signal that we are in high performance and there is no need to read the row id.

• Complex - Differed time... what actions is permitted in jdbc between INSERT and read the generated key that are not allowed by sqlite? Perhaps only those trigger the read rowid but doing another INSERT does not. This way if all I'm doing is calling INSERTs, I do not pay the ~30% price.

I don't get those points.

[yuvalp-k2view]

20-40 percent is quite huge so I think it's worth some consideration.

can you maybe share details on how you measured that, and against which version ? Trying the previous version that had support for it would also be insightful.

The benchmark is just inserting rows into a table. What exact versions would you like to see the results on?

[yuvalp-k2view]

• A bit vague but makes sense - Use the executeBatch API as a way to signal that we are in high performance and there is no need to read the row id.

JDBC has an interface where you can execute statements in batch (executeBatch). This is usually needed when there is a network between the driver and the database. This interface does not return the result from execute (rows affected for instance) and could be used as a way to circumvent the "read row id". Downside of this is that it's not widely used or known and the performance gains have less to do with "batch" but rather in a side effect of not calling rowid. On the PRO side, "to get better performance use batch" will also apply to sqlite.

• Complex - Differed time... what actions is permitted in jdbc between INSERT and read the generated key that are not allowed by sqlite? Perhaps only those trigger the read rowid but doing another INSERT does not. This way if all I'm doing is calling INSERTs, I do not pay the ~30% price.

I am wondering what are the calls to sqlite that cannot happen before calling the row id. Perhaps these are not widely used and therefor we can "delay" the call to "getRowId" to happen only if explicitly called or if one of the invalidating calls is made. For this i need to better understand the details of why the orignal "lazy" approach didn't work.

[gotson]

20-40 percent is quite huge so I think it's worth some consideration.

can you maybe share details on how you measured that, and against which version ? Trying the previous version that had support for it would also be insightful.

The benchmark is just inserting rows into a table. What exact versions would you like to see the results on?

At the minimum we would need:

• the benchmark code
• the measurements
• tested under versions:
  • 3.42.x which had the feature
  • 3.43.x which removed the feature
  • 3.45.x which reintroduced the feature

[gotson]

For this i need to better understand the details of why the orignal "lazy" approach didn't work.

Context: #329

[gotson]

Ideally all the benchmarks should be run using the same native library version: https://github.com/xerial/sqlite-jdbc/blob/master/USAGE.md#how-to-use-a-specific-native-library

[yuvalp-k2view]

So... it's actually more than double (I made a simple benchmark that had fewer columns and fewer indices so the getrowid is more pronounced). The fact that the feature was removed is not that important since it is not used by the benchmark.

Inserted 10000000 rows in 11028ms, SQLite 3.42.0
Inserted 10000000 rows in 11170ms, SQLite 3.43.0
Inserted 10000000 rows in 12080ms, SQLite 3.44.0
Inserted 10000000 rows in 25412ms, SQLite 3.45.0
Inserted 10000000 rows in 26267ms, SQLite 3.45.3
Inserted 10000000 rows in 26330ms, SQLite 3.46.0