What are the overheads of using AUTOINCREMENT for SQLite on Android?

Question

In the SQLite Documentation, it includes the following :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

and

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.

The above quotes are from SQLite Autoincrement

So what sort of impact is to be expected and by how much is AUTOINCREMENT a little slower?

Answer 1

My estimate, noting that I'm not a statistician, is that the overhead is something like 8-12% slower.

I obtained the results using 3 structurally similar and simple tables with two TEXT columns, running 10,000 inserts per each table, repeating this 5 times on 4 devices.

Table 1 (Dflt column) was created with just the two TEXT Columns (thus utilising the default ROWID).

Table 2 (AI column) was created using _id INTEGER PRIMARY KEY AUTOINCREMENT in addition to the two TEXT columns.

Table 3 (No AI column) was created using _id INTEGER PRIMARY KEY in addition to the two TEXT columns.

So Table 2 uses the slightly different ROWID selection algorithm for inserts.

The four devices used were :-

• (1) A Genymotion emulated device (Custom Tablet - 5.1.0 - API 22 - 1536x2048 )

• (2) An Onix 10" tablet (AT101-1116 )

• (3) An HTC 1 M8 (HTC_0PKV1 )

• (4) A Lenevo A10-30 tablet (Lenovo TB2-X30F )

  The results I obtained were :-

The results are more favourable when everything is run in just 1 transaction (i.e beginTransaction(); before any inserts, with setTransactionSuccessful(); and endTransaction(); after all inserts (for all tables i.e. the entire 150,000 inserts), e.g. :-

Comparison of the two tables, highlights the benefits that using transactions can have upon performance.