android-34/android/database/SQLiteDatabasePerfTest.java

/*
 * Copyright (C) 2017 The Android Open Source Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License
 */

package android.database;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.perftests.utils.BenchmarkState;
import android.perftests.utils.PerfStatusReporter;
import android.util.Log;
import androidx.test.InstrumentationRegistry;
import androidx.test.filters.LargeTest;
import androidx.test.runner.AndroidJUnit4;
import java.io.File;
import java.util.ArrayList;
import java.util.Random;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.runner.RunWith;

/**
 * Performance tests for typical CRUD operations and loading rows into the Cursor
 *
 * <p>To run: bit CorePerfTests:android.database.SQLiteDatabasePerfTest
 */
@RunWith(AndroidJUnit4.class)
@LargeTest
public class SQLiteDatabasePerfTest {
    // TODO b/64262688 Add Concurrency tests to compare WAL vs DELETE read/write
    private static final String DB_NAME = "dbperftest";
    private static final int DEFAULT_DATASET_SIZE = 1000;

    @Rule
    public PerfStatusReporter mPerfStatusReporter = new PerfStatusReporter();
    private SQLiteDatabase mDatabase;
    private Context mContext;

    @Before
    public void setUp() {
        mContext = InstrumentationRegistry.getTargetContext();
        mContext.deleteDatabase(DB_NAME);

        createOrOpenTestDatabase(
                SQLiteDatabase.JOURNAL_MODE_TRUNCATE, SQLiteDatabase.SYNC_MODE_FULL);
    }

    @After
    public void tearDown() {
        mDatabase.close();
        mContext.deleteDatabase(DB_NAME);
    }

    private void createOrOpenTestDatabase(String journalMode, String syncMode) {
        SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder();
        File dbFile = mContext.getDatabasePath(DB_NAME);
        if (journalMode != null) {
            paramsBuilder.setJournalMode(journalMode);
        }
        if (syncMode != null) {
            paramsBuilder.setSynchronousMode(syncMode);
        }
        paramsBuilder.addOpenFlags(SQLiteDatabase.CREATE_IF_NECESSARY);

        mDatabase = SQLiteDatabase.openDatabase(dbFile, paramsBuilder.build());
        mDatabase.execSQL("CREATE TABLE T1 "
                + "(_ID INTEGER PRIMARY KEY, COL_A INTEGER, COL_B VARCHAR(100), COL_C REAL)");
        mDatabase.execSQL("CREATE TABLE T2 ("
                + "_ID INTEGER PRIMARY KEY, COL_A VARCHAR(100), T1_ID INTEGER,"
                + "FOREIGN KEY(T1_ID) REFERENCES T1 (_ID))");
    }

    @Test
    public void testSelect() {
        insertT1TestDataSet();

        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        Random rnd = new Random(0);
        while (state.keepRunning()) {
            int index = rnd.nextInt(DEFAULT_DATASET_SIZE);
            try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 "
                    + "WHERE _ID=?", new String[]{String.valueOf(index)})) {
                assertTrue(cursor.moveToNext());
                assertEquals(index, cursor.getInt(0));
                assertEquals(index, cursor.getInt(1));
                assertEquals("T1Value" + index, cursor.getString(2));
                assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d);
            }
        }
    }

    @Test
    public void testSelectCacheMissRate() {
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        insertT1TestDataSet();

        ArrayList<String> queryPool = new ArrayList<>();
        queryPool.add("SELECT _ID, COL_A, COL_B, COL_C FROM T1 WHERE _ID=?");
        queryPool.add("SELECT _ID FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_A FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_B FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_C FROM T1 WHERE _ID=?");
        queryPool.add("SELECT _ID, COL_A FROM T1 WHERE _ID=?");
        queryPool.add("SELECT _ID, COL_B FROM T1 WHERE _ID=?");
        queryPool.add("SELECT _ID, COL_C FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_A, COL_B FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_A, COL_C FROM T1 WHERE _ID=?");
        queryPool.add("SELECT COL_B, COL_C FROM T1 WHERE _ID=?");
        while (state.keepRunning()) {
            Random rnd = new Random(0);

            int queries = 1000;
            for (int iQuery = 0; iQuery < queries; ++iQuery) {
                int queryIndex = rnd.nextInt(queryPool.size());
                int index = rnd.nextInt(DEFAULT_DATASET_SIZE);

                try (Cursor cursor = mDatabase.rawQuery(
                             queryPool.get(queryIndex), new String[] {String.valueOf(index)})) {
                    assertTrue(cursor.moveToNext());
                }
            }
        }

        Log.d("testSelectMemory",
                "cacheMissRate: " + mDatabase.getStatementCacheMissRate()
                        + "Total Statements: " + mDatabase.getTotalPreparedStatements()
                        + ". Misses: " + mDatabase.getTotalStatementCacheMisses());

        // Make sure caching is working and our miss rate should definitely be less than 100%
        // however, we would expect this number to be actually closer to 0.
        assertTrue(mDatabase.getStatementCacheMissRate() < 1);
        mDatabase.close();
        mContext.deleteDatabase(DB_NAME);
    }

    @Test
    public void testSelectMultipleRows() {
        insertT1TestDataSet();

        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
        Random rnd = new Random(0);
        final int querySize = 50;
        while (state.keepRunning()) {
            int index = rnd.nextInt(DEFAULT_DATASET_SIZE - querySize - 1);
            try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 "
                            + "WHERE _ID BETWEEN ? and ? ORDER BY _ID",
                    new String[]{String.valueOf(index), String.valueOf(index + querySize - 1)})) {
                int i = 0;
                while(cursor.moveToNext()) {
                    assertEquals(index, cursor.getInt(0));
                    assertEquals(index, cursor.getInt(1));
                    assertEquals("T1Value" + index, cursor.getString(2));
                    assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d);
                    index++;
                    i++;
                }
                assertEquals(querySize, i);
            }
        }
    }

    @Test
    public void testCursorIterateForward() {
        // A larger dataset is needed to exceed default CursorWindow size
        int datasetSize = DEFAULT_DATASET_SIZE * 50;
        insertT1TestDataSet(datasetSize);

        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
        while (state.keepRunning()) {
            try (Cursor cursor = mDatabase
                    .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) {
                int i = 0;
                while(cursor.moveToNext()) {
                    assertEquals(i, cursor.getInt(0));
                    assertEquals(i, cursor.getInt(1));
                    assertEquals("T1Value" + i, cursor.getString(2));
                    assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d);
                    i++;
                }
                assertEquals(datasetSize, i);
            }
        }
    }

    @Test
    public void testCursorIterateBackwards() {
        // A larger dataset is needed to exceed default CursorWindow size
        int datasetSize = DEFAULT_DATASET_SIZE * 50;
        insertT1TestDataSet(datasetSize);

        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
        while (state.keepRunning()) {
            try (Cursor cursor = mDatabase
                    .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) {
                int i = datasetSize - 1;
                while(cursor.moveToPosition(i)) {
                    assertEquals(i, cursor.getInt(0));
                    assertEquals(i, cursor.getInt(1));
                    assertEquals("T1Value" + i, cursor.getString(2));
                    assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d);
                    i--;
                }
                assertEquals(-1, i);
            }
        }
    }

    @Test
    public void testInnerJoin() {
        mDatabase.setForeignKeyConstraintsEnabled(true);
        mDatabase.beginTransaction();
        insertT1TestDataSet();
        insertT2TestDataSet();
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();

        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        Random rnd = new Random(0);
        while (state.keepRunning()) {
            int index = rnd.nextInt(1000);
            try (Cursor cursor = mDatabase.rawQuery(
                    "SELECT T1._ID, T1.COL_A, T1.COL_B, T1.COL_C, T2.COL_A FROM T1 "
                    + "INNER JOIN T2 on T2.T1_ID=T1._ID WHERE T1._ID = ?",
                    new String[]{String.valueOf(index)})) {
                assertTrue(cursor.moveToNext());
                assertEquals(index, cursor.getInt(0));
                assertEquals(index, cursor.getInt(1));
                assertEquals("T1Value" + index, cursor.getString(2));
                assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d);
                assertEquals("T2Value" + index, cursor.getString(4));
            }
        }
    }

    /**
     * This test measures the insertion of a single row into a database using DELETE journal and
     * synchronous modes.
     */
    @Test
    public void testInsert() {
        insertT1TestDataSet();

        testInsertInternal("testInsert");
    }

    @Test
    public void testInsertWithPersistFull() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL);
        insertT1TestDataSet();
        testInsertInternal("testInsertWithPersistFull");
    }

    private void testInsertInternal(String traceTag) {
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        ContentValues cv = new ContentValues();
        cv.put("_ID", DEFAULT_DATASET_SIZE);
        cv.put("COL_B", "NewValue");
        cv.put("COL_C", 1.1);
        String[] deleteArgs = new String[] {String.valueOf(DEFAULT_DATASET_SIZE)};

        while (state.keepRunning()) {
            android.os.Trace.beginSection(traceTag);
            assertEquals(DEFAULT_DATASET_SIZE, mDatabase.insert("T1", null, cv));
            state.pauseTiming();
            assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs));
            state.resumeTiming();
            android.os.Trace.endSection();
        }
    }

    /**
     * This test measures the insertion of a single row into a database using WAL journal mode and
     * NORMAL synchronous mode.
     */
    @Test
    public void testInsertWithWalNormalMode() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL);
        insertT1TestDataSet();

        testInsertInternal("testInsertWithWalNormalMode");
    }

    /**
     * This test measures the insertion of a single row into a database using WAL journal mode and
     * FULL synchronous mode. The goal is to see the difference between NORMAL vs FULL sync modes.
     */
    @Test
    public void testInsertWithWalFullMode() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_FULL);

        insertT1TestDataSet();

        testInsertInternal("testInsertWithWalFullMode");
    }

    /**
     * This test measures the insertion of a multiple rows in a single transaction using WAL journal
     * mode and NORMAL synchronous mode.
     */
    @Test
    public void testBulkInsertWithWalNormalMode() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL);
        testBulkInsertInternal("testBulkInsertWithWalNormalMode");
    }

    @Test
    public void testBulkInsertWithPersistFull() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL);
        testBulkInsertInternal("testBulkInsertWithPersistFull");
    }

    /**
     * This test measures the insertion of a multiple rows in a single transaction using TRUNCATE
     * journal mode and FULL synchronous mode.
     */
    @Test
    public void testBulkInsert() {
        testBulkInsertInternal("testBulkInsert");
    }

    private void testBulkInsertInternal(String traceTag) {
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        String[] statements = new String[DEFAULT_DATASET_SIZE];
        for (int i = 0; i < DEFAULT_DATASET_SIZE; ++i) {
            statements[i] = "INSERT INTO T1 VALUES (?,?,?,?)";
        }

        while (state.keepRunning()) {
            android.os.Trace.beginSection(traceTag);
            mDatabase.beginTransaction();
            for (int i = 0; i < DEFAULT_DATASET_SIZE; ++i) {
                mDatabase.execSQL(statements[i], new Object[] {i, i, "T1Value" + i, i * 1.1});
            }
            mDatabase.setTransactionSuccessful();
            mDatabase.endTransaction();
            android.os.Trace.endSection();

            state.pauseTiming();
            mDatabase.execSQL("DELETE FROM T1");
            state.resumeTiming();
        }
    }

    @Test
    public void testDelete() {
        insertT1TestDataSet();
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
        String[] deleteArgs = new String[]{String.valueOf(DEFAULT_DATASET_SIZE)};
        Object[] insertsArgs = new Object[]{DEFAULT_DATASET_SIZE, DEFAULT_DATASET_SIZE,
                "ValueToDelete", 1.1};

        while (state.keepRunning()) {
            state.pauseTiming();
            mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)", insertsArgs);
            state.resumeTiming();
            assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs));
        }
    }

    /**
     * This test measures the update of a random row in a database.
     */
    @Test
    public void testUpdateWithWalNormalMode() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL);
        insertT1TestDataSet();
        testUpdateInternal("testUpdateWithWalNormalMode");
    }

    @Test
    public void testUpdateWithPersistFull() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL);
        insertT1TestDataSet();
        testUpdateInternal("testUpdateWithPersistFull");
    }

    @Test
    public void testUpdate() {
        insertT1TestDataSet();
        testUpdateInternal("testUpdate");
    }

    private void testUpdateInternal(String traceTag) {
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();

        Random rnd = new Random(0);
        int i = 0;
        ContentValues cv = new ContentValues();
        String[] argArray = new String[1];
        while (state.keepRunning()) {
            android.os.Trace.beginSection(traceTag);
            int id = rnd.nextInt(DEFAULT_DATASET_SIZE);
            cv.put("COL_A", i);
            cv.put("COL_B", "UpdatedValue");
            cv.put("COL_C", i);
            argArray[0] = String.valueOf(id);
            assertEquals(1, mDatabase.update("T1", cv, "_ID=?", argArray));
            i++;
            android.os.Trace.endSection();
        }
    }

    /**
     * This test measures a multi-threaded read-write environment where there are 2 readers and
     * 1 writer in the database using TRUNCATE journal mode and FULL syncMode.
     */
    @Test
    public void testMultithreadedReadWrite() {
        insertT1TestDataSet();
        performMultithreadedReadWriteTest();
    }

    private void doReadLoop(int totalIterations) {
        Random rnd = new Random(0);
        int currentIteration = 0;
        while (currentIteration < totalIterations) {
            android.os.Trace.beginSection("ReadDatabase");
            int index = rnd.nextInt(DEFAULT_DATASET_SIZE);
            try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 "
                                 + "WHERE _ID=?",
                         new String[] {String.valueOf(index)})) {
                cursor.moveToNext();
                cursor.getInt(0);
                cursor.getInt(1);
                cursor.getString(2);
                cursor.getDouble(3);
            }
            ++currentIteration;
            android.os.Trace.endSection();
        }
    }

    private void doReadLoop(BenchmarkState state) {
        Random rnd = new Random(0);
        while (state.keepRunning()) {
            android.os.Trace.beginSection("ReadDatabase");
            int index = rnd.nextInt(DEFAULT_DATASET_SIZE);
            try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 "
                                 + "WHERE _ID=?",
                         new String[] {String.valueOf(index)})) {
                cursor.moveToNext();
                cursor.getInt(0);
                cursor.getInt(1);
                cursor.getString(2);
                cursor.getDouble(3);
            }
            android.os.Trace.endSection();
        }
    }

    private void doUpdateLoop(int totalIterations) {
        SQLiteDatabase db = mContext.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
        Random rnd = new Random(0);
        int i = 0;
        ContentValues cv = new ContentValues();
        String[] argArray = new String[1];

        while (i < totalIterations) {
            android.os.Trace.beginSection("UpdateDatabase");
            int id = rnd.nextInt(DEFAULT_DATASET_SIZE);
            cv.put("COL_A", i);
            cv.put("COL_B", "UpdatedValue");
            cv.put("COL_C", i);
            argArray[0] = String.valueOf(id);
            db.update("T1", cv, "_ID=?", argArray);
            i++;
            android.os.Trace.endSection();
        }
    }

    /**
     * This test measures a multi-threaded read-write environment where there are 2 readers and
     * 1 writer in the database using WAL journal mode and NORMAL syncMode.
     */
    @Test
    public void testMultithreadedReadWriteWithWalNormal() {
        recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL);
        insertT1TestDataSet();

        performMultithreadedReadWriteTest();
    }

    private void performMultithreadedReadWriteTest() {
        int totalBGIterations = 10000;
        // Writer - Fixed iterations to avoid consuming cycles from mainloop benchmark iterations
        Thread updateThread = new Thread(() -> { doUpdateLoop(totalBGIterations); });

        // Reader 1 - Fixed iterations to avoid consuming cycles from mainloop benchmark iterations
        Thread readerThread = new Thread(() -> { doReadLoop(totalBGIterations); });

        updateThread.start();
        readerThread.start();

        // Reader 2
        BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
        doReadLoop(state);

        try {
            updateThread.join();
            readerThread.join();
        } catch (Exception e) {
        }
    }

    private void insertT1TestDataSet() {
        insertT1TestDataSet(DEFAULT_DATASET_SIZE);
    }

    private void insertT1TestDataSet(int size) {
        mDatabase.beginTransaction();
        for (int i = 0; i < size; i++) {
            mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)",
                    new Object[]{i, i, "T1Value" + i, i * 1.1});
        }
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
    }

    private void insertT2TestDataSet() {
        mDatabase.beginTransaction();
        for (int i = 0; i < DEFAULT_DATASET_SIZE; i++) {
            mDatabase.execSQL("INSERT INTO T2 VALUES (?, ?, ?)",
                    new Object[]{i, "T2Value" + i, i});
        }
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
    }

    private void recreateTestDatabase(String journalMode, String syncMode) {
        mDatabase.close();
        mContext.deleteDatabase(DB_NAME);
        createOrOpenTestDatabase(journalMode, syncMode);
    }
}