SQLite Prepared Statement (Speed)

Databases
1

Which would you expect to be faster?

Option 1

ps.BindType(0,SQLitePreparedStatement.SQLITE_INTEGER) ps.Bind(0,MaxID)

Option 2

ps.BindType(0,SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0,cstr(MaxID))

I expected option 1 to be faster but found option 2 is about 5-10% faster!

2

And … option 2 can be used conditionally, never mind.
Actually SQLite = TEXT

3

I assume you meant like this:

"INSERT INTO MyTable(ID, Field1) VALUES(" + cstr(MaxID) + ", ?"

I had my code written like that but added the MaxID as a bind values because it allowed me to reuse my Prepared Statement, saving a significant amount of time.

4

I did some testing about this with PostgresSQL.
I am curious how much time you save with reusing prepared statements on SQLite. Don’t think it will be that much if you have the sqlite file locally.

5

Inserting 100,000 records into an in memory db i got the following results. Reusing the PS saved more time than using a transaction.

6.4 seconds 6.15 seconds using transaction 4% saving 4.65 seconds using transaction and reusing PS 24% additional savings

6

I wonder what the results would have been doing an insert rather than a select?

7

My guess was that the PostgreSQL engine is so fast that the biggest benefit of reusing prepared statements comes from saving a lot of data-communication, especially when the database is remote.

8

using cstr looks wrong as that is localized.
SQLite can work with integers and should be more efficient with them.
But things highly depend on cache sizes and indices.

9

This is inserting MaxID into and integer PK column. Standard Xojo in memory SQLite db.

Not exactly sure what you mean about this.

10

cstr is localized while str is not.
And database nornally wants us format.