Take benefit of SQLite optimization when statement is cached vs when its used once or few time and finalized quickly

[khanaffan]

Allow passing SQLITE_PREPARE_PERSISTENT to prepared statement when it is prepared with intention to be cached for multiple use.

The option avoid using lookaside memory which is use for shorter lived statement. lookaside memory allow faster prepare for single use query.

Also, note that for single-use statements, there is no guarantee of utilizing the lookaside memory pool if their state does not fit into a slot in the pool or if the pool has no empty slots. In such cases, it's allocated on the heap. By default, the lookaside memory is set to 48KB (40 slots of 1200 bytes), which is generally exhausted by cached statements. Allowing lookaside memory to be used by single-use statements can make single-use statement preparation, stepping, and finalization very fast and may not require any malloc at all.

With this change, we can prevent caching unnecessary SQLite statements and utilize single-use statements where appropriate, as they are faster to prepare and finalize compared to those allocated on the heap. The persistent flag will signal the query planner to allocate the statement on the heap when we intent to cache it.

Note: SQLite internally runs SQLite statements using either the execute or statement interface, which are also allocated on the heap. This occurs as the application has already exhausted 40 slots in the lookaside memory.

All of the following now use SQLITE_PREPARE_PERSISTENT option when preparing sqlite statement.

• SqliteStatementCache
• ECSqlStatementCache
• ConcurrentQuery
• InstanceQuery
• TypeScript Sqlite/ECSql Statement caches

We should also name the IModelDb.withPreparedStatement() as IModelDb.withCachedStatement() while keep same name for IModelDb.withStatement(). Thought this not part of this PR.

imodel-native: https://github.com/iTwin/imodel-native/pull/791

[mergify[bot]]

This pull request is now in conflicts. Could you fix it @khanaffan? 🙏 To fixup this pull request, you can check out it locally. See documentation: https://help.github.com/articles/checking-out-pull-requests-locally/

[mergify[bot]]

This pull request is now in conflicts. Could you fix it @khanaffan? 🙏 To fixup this pull request, you can check out it locally. See documentation: https://help.github.com/articles/checking-out-pull-requests-locally/

[mergify[bot]]

This pull request is now in conflicts. Could you fix it @khanaffan? 🙏 To fixup this pull request, you can check out it locally. See documentation: https://help.github.com/articles/checking-out-pull-requests-locally/