What Are the Overheads of Using Autoincrement for SQLite on Android

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

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.

SQLITE: Best practices about using AUTOINCREMENT

As recommended in the documentation, it is better to not use AUTOINCREMENT unless you need to ensure that the alias of the rowid (aka the id) is greater then any that have been added. However, (in normal use) it's a moot point as such, as even without AUTOINCREMENT, until you have reached 9223372036854775807 rows then a higher rowid/id will result.

If you do reach the id/rowid of 9223372036854775807, then that's it if you have AUTOINCREMENT coded, as an SQLITE_FULL exception will happen. Whilst without AUTOINCREMENT attempts will be made to get an unused id/rowid.

AUTOINCREMENT adds a row (table if required) to sqlite_sequence that records the highest allocated id. The difference between with and without AUTOINCREMENT is that the sqlite_sequecence table is referenced, whilst without AUTOINCREMENT the isn't. So if a row is deleted that has the highest id AUTOINCREMENT gets the highest ever allocated id from the sqlite_sequence table (and user the greater of that or max(rowid)), without doesn't so it uses the highest in the table where the row is being inserted (equivalent to max(rowid)).

With limited testing an overhead of 8-12% was found to be the overhead as per What are the overheads of using AUTOINCREMENT for SQLite on Android?
.

How to make AUTO_INCREMENT on Android SQLite database?

You don't have to parse anything. If the column was created as AUTOINCREMENT, just pass the other values:

db.execSQL("insert into "
        + TABLE_NAME
        + "(contact_id, contact_name, number_type, contact_number, duration, date, current_time, cont) "
        + "values( "+ cId + ", " + cName + ", " + numType + ", "
        + cNum + ", " + dur + ", " + date + ", " + currTime + ", ? )");

By the way, it's always recommended to insert data using the Android's insert method:

ContentValues values = new ContentValues();
values.put("contact_id", cId);
values.put("contact_name", cName);
// etc.
db.insert(TABLE_NAME, null, values);

Sqlite query in Android for auto increment and default values

This should be the correct syntax:

CREATE TABLE table_name (Id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, date DATE NOT NULL, status INTEGER DEFAULT 0)

Autoincrement in SQLite tables

You can see here, as it was commented in your question.

However, if you are in a dilemma what to choose between these options, SQLite recommends that you should not use AUTOINCREMENT attribute because:

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.

More info you can read here.

Is PrimaryKey's autoGenerate exactly equivalent to SQLite's AUTOINCREMENT?

Is marking a primary key with @PrimaryKey(autoGenerate = true) exactly the same as if you had used PRIMARY KEY AUTOINCREMENT in an SQL statement?

Yes, as using autoGenerate=true adds the AUTOINCREMENT keyword.

But

as if setting it false will prevent SQLite from generating the key.

Is false.

If a class is:-

• annotated with @Entity, and
• the column/variable/member is annotated with @PrimaryKey, and
• if the type resolves to an integer type
  
  • (byte .... double, primitive or Object (e.g. Double))

then the value can be generated (it is INTEGER PRIMARY KEY that makes the column a special column that can be generated as that column is then an alias of the rowid (a normally hidden column)).

AUTOINCREMENT is only applicable to aliases of the rowid (i.e. INTEGER PRIMARY KEY). It does not determine whether the value can be generated (in the absence of a value for the column or when the value is null).

What AUTOINCREMENT does is add an additional rule when generating the value. That rule being that the value MUST be higher than any ever used for that table.

There are subtle differences.

Without AUTOINCREMENT

• deleting the row with the highest value, frees that value for subsequent use (and would be used to generate the value still higher than any other value that exists at that time), and
• should the highest value (9223372036854775807) be reached SQLite will try to find a free lower value, and
• lastly it is possible to double the range of values by using negative values.

With AUTOINCREMENT

• deleting the row with the highest value does not free that value for subsequent use

• should the highest value (9223372036854775807) be reached then subsequent attempts to insert with a generated value will fail with an SQLITE FULL error.

  • If you insert 1 row with a value of 9223372036854775807 then that's the only row that can be inserted.

• negative values cannot be generated (can still be used)

• an additional table is required (sqlite_sequence), which is automatically created by SQLite, that will have a row per table with AUTOINCREMENT. The highest used value is stored in the row. So whenever inserting when the value is to be generated requires the respective row to be retrieved and the value obtained, after insertion the value has to be updated. As such there are overheads associated with using AUTOINCREMENT.

• Note the above is assuming that methods to circumvent SQLite's in-built handling are not circumvented (such as updating values in the sqlite_sequence table).

I would always advocate using (not using autoGenerate=true) e.g.

@PrimaryKey
Long id_column=null;

or

@PrimaryKey
var id_column: Long?=null

thus an @Insert (convenience insert) will autogenerate if no value is given for the id_column.

---

Demo

Consider the following two @Entity annotated classes (with and without autoGenerate=true) :-

AutoInc:-

@Entity
data class AutoInc(
    @PrimaryKey(autoGenerate = true)
    val id: Long?=null,
    val other: String
)

NoAutoInc:-

@Entity
data class NoAutoInc(
    @PrimaryKey
    var id: Long?=null,
    var other:String
)

Room (after compiling and looking at the generated java in the class that is the same name as the @Database annotated class) has the following in the createAllTables method/function:-

    _db.execSQL("CREATE TABLE IF NOT EXISTS `AutoInc` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `other` TEXT NOT NULL)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `NoAutoInc` (`id` INTEGER, `other` TEXT NOT NULL, PRIMARY KEY(`id`))");

i.e. the only difference is the AUTOINCREMENT keyword.

Then consider the following code :-

    /* Typical  where the id will be generated */
    dao.insert(AutoInc(other = "A"))
    dao.insert(AutoInc(null,other = "B"))
    dao.insert(NoAutoInc(other ="A"))
    dao.insert(NoAutoInc(null, other = "B"))

    /* Beware */
    /* Room interprets types different ways
        here 0 is taken to be 0 as id is an Object
        if long (Java) then 0 will be generated id
        getters/setters are taken in to consideration when determining type
    * */
    dao.insert(AutoInc(0,other = "W"))
    dao.insert(NoAutoInc(0,other ="W"))

    /* Unusual */
    dao.insert(AutoInc(-100,"X"))
    dao.insert(NoAutoInc(-100,other ="X"))
    dao.insert(AutoInc(9223372036854775807,"Y")) /* The maximum value for an id */
    dao.insert(NoAutoInc(9223372036854775807,"Y")) /* The maximum value for an id */

When run then the tables (via Android Studio's App Inspection) are:-

AutInc:-

Note the Z row has not been added due to :-

E/SQLiteLog: (13) statement aborts at 4: [INSERT OR ABORT INTO `AutoInc` (`id`,`other`) VALUES (?,?)] database or disk is full

However, the disk isn't full as Disk Explorer shows:-

It's by no means full as Disk Explorer shows (and of course the subsequent step works inserting a row into the database):-

and

NoAutInc

Here the Z row has been added with a generated id based upon SQLite finding an unused value due to the highest allowable value for an id having been reached as opposed to the failure due to the disk/table full.

Auto increment is not working in SQLite database

Using your SQL for the table creation i.e.

`sqLiteDatabase.execSQL("create table myproduct ("+COL1_NAME+" integer primary key  autoincrement, "+COL2_NAME+" text , "+COL3_NAME+" text , "+COL4_NAME+" text)");`

And then using the following to test :-

            dbhlp = new SO45290855(this);

            SQLiteDatabase dbx = dbhlp.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put(SO45290855.COL2_NAME,"xxxx");
            cv.put(SO45290855.COL3_NAME,"yyyy");
            cv.put(SO45290855.COL4_NAME,"zzzz");
            dbx.insert(SO45290855.TABLE_NAME,null,cv);
            ContentValues cv2 = new ContentValues();
            cv2.put(SO45290855.COL2_NAME,"xxxx");
            cv2.put(SO45290855.COL3_NAME,"yyyy");
            cv2.put(SO45290855.COL4_NAME,"zzzz");
            dbx.insert(SO45290855.TABLE_NAME,null,cv);

            Cursor csr = dbx.query(SO45290855.TABLE_NAME,null,null,null,null,null,null);

            while (csr.moveToNext()) {
                for (int i=0; i < csr.getColumnCount();i++) {
                    Log.d("CsrPos_" + Integer.toString(csr.getPosition()),
                            "Column=" + csr.getColumnName(i) +
                                    " Data=" + csr.getString(i)
                    );
                }
            }

the output given is :-

07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productId Data=1
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productSalary Data=xxxx
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=companyname Data=yyyy
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productName Data=zzzz
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productId Data=2
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productSalary Data=xxxx
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=companyname Data=yyyy
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productName Data=zzzz

SO45290855 being a copy of your database class

As such, due to the first line showing productId has a value of 1 and also that the 5th line shows the porductId, in the 2nd row, has a value of 2, in principle the code you have should create the same.

Therefore it is very likely that you have amended the code to change the structure but have not realised that onCreate basically runs once for the lifetime of the database file and thus unless you have deleted the database file, changes to the structure will not be applied.

The likely solution is to clear the App's data or to uninstall the App and rerun.

Further to this you very likely do not need to code AUTOINCREMENT using column_name INTEGER PRIMARY KEY will result in an incrementing column.

I'd suggest reading SQLite Autoincrement which includes

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.

SQLite use of autoincrement in a small table

The recommendation is not "Never, ever use this, and we have no idea why we even allow it." It's simply a statement that it has non-trivial costs, and therefore it should only be used when needed.

If you need it, then you need it.

INTEGER PRIMARY KEY AUTOINCREMENT in Android not working

You are not setting the _id in your object d -

Add this line in your for loop -

d.set_id(c.getInt(c.getColumnIndex("_ID")));

---

Related Topics