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

by

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 – 1536×2048
    )

  • (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 :-

enter image description here

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. :-

enter image description here

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

More Related Contents:

  1. Need a bit of assistance [closed]
  2. How to add multiple edit text and spinner data into SQLite database?
  3. CursorOutofBoundsException Index -1 requested with size of 0
  4. Why does the application crash when I press the button to insert the values in the table? [duplicate]
  5. Save data in a SQLite database with the original format and view those data again with that form format
  6. When does SQLiteOpenHelper onCreate() / onUpgrade() run?
  7. Ship an application with a database
  8. Retrieve database or any other file from the Internal Storage using run-as
  9. How to access data/data folder in Android device?
  10. View contents of database file in Android Studio
  11. Recommended way / order to read data from a webservice, parse that data and insert it in a SQLite db
  12. android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
  13. Android SQLite issue – table … has no column named
  14. How to autoincrement versionCode in Android Gradle
  15. How to perform an SQLite query within an Android application?
  16. How to use Room Persistence Library with pre-populated database?
  17. Android: column ‘_id’ does not exist
  18. How can I insert image in a sqlite database
  19. How to store and retrieve a byte array (image data) to and from a SQLite database?
  20. A pre-populated database does not work at API 28 throws “no such table” exception
  21. SQLite returned an error code of 14
  22. How to store sqlite database directly on sdcard
  23. Attempt to reopen an already-closed object sqlitedatabase
  24. How insert image in room persistence library?
  25. How to convert milliseconds to date in SQLite
  26. How to check database on not rooted android device
  27. PowerManager.PARTIAL_WAKE_LOCK android
  28. Android Gradle Could not reserve enough space for object heap
  29. Firebase (FCM): open activity and pass data on notification click. android
  30. dynamic listview adding “Load more items” at the end of scroll

Leave a Comment