Performance với SQLite

1. Methodology

Mỗi thí nghiệm liên quan đến việc so sánh hai hoặc nhiều cách insert 1000, 10.000 và 100.000 bản ghi ngẫu nhiên được tạo thành hai loại bảng khác nhau.

• Một bảng rất đơn giản (simple) bao gồm một cột số nguyên duy nhất.
• Một bảng (tracks) thực tế hơn mô tả một tập hợp các bản nhạc, mỗi bài chứa một id, tiêu đề, thời lượng, lời bài hát, vv ... ID id là PRIMARY KEY của bảng này.

Các kết quả được tính bằng cách theo dõi thời gian đã trôi qua trong khi tất cả các insert cho việc lặp lại kích thước hiện tại đang chạy. Thời gian kết nối với cơ sở dữ liệu và để wipe table sau mỗi lần lặp.

1. Exploration

db.insert() có và không có một explicit transaction

Tài liệu đào tạo chính thức cho các ràng buộc SQLite của Android cung cấp một ví dụ về việc populating một bảng bằng cách sử dụng phương thức insert () được cung cấp bởi đối tượng SQLiteDatabase. Thật không may họ không đưa ra bất kỳ lời khuyên về cách có nhiều insert cùng một lúc. Cách tiếp cận cơ bản cho bảng simple sẽ như sau:

ContentValues values = new ContentValues(1);
for (int i = 0; i < numIterations; i++) {
    values.put('val', random.nextInt());
    db.insert("inserts_1", null, values);
}

Tôi nghĩ rằng có thể có một số hiệu suất thu được bằng cách chạy các cuộc gọi đến db.insert () trong một transaction, vì vậy tôi đã thực hiện thí nghiệm đầu tiên của mình:

db.beginTransaction();
ContentValues values = new ContentValues(1);
for (int i = 0; i < numIterations; i++) {
    values.put('val', random.nextInt());
    db.insert("inserts_1", null, values);
}
db.setTransactionSuccessful();
db.endTransaction();

Nhìn vào biểu đồ, rõ ràng là gói một loạt các lệnh insert () trong một transaction cải thiện đáng kể hiệu suất so với việc insert mà không có giao dịch gói. Nhưng tại sao nó nhanh hơn rất nhiều? Nó chỉ ra rằng trừ khi bạn thực hiện các truy vấn một cách rõ ràng giữa các cuộc gọi đến beginTransaction () và endTransaction (), chính SQLite sẽ gói mọi truy vấn với một giao dịch ẩn:

Không thể thay đổi cơ sở dữ liệu ngoại trừ trong một transaction. Bất kỳ lệnh nào thay đổi cơ sở dữ liệu (về cơ bản, bất kỳ câu lệnh SQL nào khác với SELECT) sẽ tự động bắt đầu một giao dịch nếu không có hiệu lực. Tự động bắt đầu các giao dịch được cam kết khi truy vấn cuối cùng kết thúc.

Điều quan trọng cần lưu ý là SQLite chỉ viết các lệnh insert vào disk khi transaction đã được thực hiện. Vì vậy, nếu bạn có thể giảm thiểu số lượng các transaction (bất kể chúng được bắt đầu rõ ràng hoặc ngầm), bạn sẽ giảm thiểu truy cập tới disk và tối đa hóa hiệu suất. Chuyển đổi dữ liệu trong biểu đồ thành các bản ghi insert vào một giây:

• Không có giao dịch rõ ràng: ~ 75 track record dõi mỗi giây.
• Có giao dịch rõ ràng: ~ 950 track record dõi mỗi giây.

Một cải tiến 10x, chỉ với 3 dòng mã! Bây giờ chúng ta biết rằng sử dụng các transaction là một lợi thế rất lớn, chúng tôi sẽ sử dụng chúng từ bây giờ khi chúng ta chuyển sang các cách khác để chèn dữ liệu.

**db.execSQL() **

Một phương pháp tiếp cận bởi SQLiteDatabase sẽ cho phép chúng ta chèn dữ liệu vào các bảng là db.execSQL (String, Object []). Nó được cung cấp như là một cách để thực hiện những hành động không chọn lọc. Đây là code thử nghiệm:

db.beginTransaction();
Object[] values = new Object[1];
for (int i = 0; i < numIterations; i++) {
    values[0] = random.nextInt();
    db.execSQL("INSERT INTO inserts_1 (val) VALUES (?)", values);
}
db.setTransactionSuccessful();
db.endTransaction();

Trong thử nghiệm này, bằng cách sử dụng db.execSQL (), chúng ta có thể cải thiện record một chút trong 1 giây:

• db.insert(): ~850 track records per second.
• db.execSQL(): ~925 track records per second.

Db.insert () cơ bản là cú pháp tạo ra các câu lệnh SQL cho bạn. Lớp trừu tượng đó, không tốn kém, nhưng không phải là miễn phí.

Batched Inserts với db.execSQL()

Chúng tôi đã có một số cải thiện hiệu suất làm việc với các raw statement và db.execSQL (), vì vậy tôi nghĩ rằng bây giờ chúng ta đang xây dựng các tuyên bố: nếu chúng ta chèn nhiều hơn một bản ghi cùng một lúc?

Tôi đã từ chối ý tưởng ban đầu bởi vì SQLite là một công cụ cơ sở dữ liệu in-process, chúng tôi không nhất thiết phải tiết kiệm bất cứ thứ gì bằng cách batching insert (không giống với các máy chủ cơ sở dữ liệu, nơi mà bạn phải chịu độ trễ của mạng với mỗi câu lệnh). Nỗ lực đầu tiên trong việc đặt một batch insert(chèn hàng loạt):

db.beginTransaction();
Object[] values = new Object[numIterations];
StringBuilder valuesBuilder = new StringBuilder();
for (int i = 0; i < numIterations; i++) {
    if (i != 0) {
        valuesBuilder.append(", ");
    }
    values[i] = mRandom.nextInt();
    valuesBuilder.append("(?)");
}

db.execSQL(
    "INSERT INTO inserts_1 (val) VALUES "+valuesBuilder.toString(), 
    values
);
db.setTransactionSuccessful();
db.endTransaction();

Quá nhiều SQL variables?!

Nó chỉ ra rằng trong mã nguồn SQLite, họ đặt một giới hạn cứng về số lượng các biến được phép trong một tuyên bố chuẩn bị. Từ sqlite3.c:

/*
** The maximum value of a ?nnn wildcard that the parser will accept.
*/
#ifndef SQLITE_MAX_VARIABLE_NUMBER
# define SQLITE_MAX_VARIABLE_NUMBER 999
#endif

db.beginTransaction();
doInsertions(db, numIterations);
db.setTransactionSuccessful();
db.endTransaction();
// ... elsewhere in the class ...
void doInsertions(SQLiteDatabase db, int numInsertions) {
    if (total > 999) {
        doInsertions(db, numInsertions - 999);
        numInsertions = 999;
    }
    Object[] values = new Object[numInsertions];
    StringBuilder valuesBuilder = new StringBuilder();

    for (int i = 0; i < numInsertions; i++) {
        if (i != 0) {
            valuesBuilder.append(", ");
        }
        values[i] = mRandom.nextInt();
        valuesBuilder.append("(?)");
    }
    
    db.execSQL(
        "INSERT INTO inserts_1 (val) VALUES " 
            +valuesBuilder.toString(), 
        values
    );
}

Sử dụng đệ quy làm cho nó siêu dễ dàng, nhưng nếu đó không phải là phong cách của bạn hoặc nó sẽ không làm việc cho tình hình của bạn, một vòng lặp cũng là hợp lý.

Wow! Nó chỉ ra rằng mặc dù chúng tôi không tiết kiệm bất kỳ độ trễ nào của mạng với chèn hàng loạt, nhưng chúng tôi nhận được một ít hiệu quả bổ sung vì không phải làm nhiều individual statement.

Một điều cần lưu ý, tuy nhiên: càng nhiều cột trong bảng của bạn, ít lợi ích bạn nhận được ra khỏi chèn hàng loạt. Điều này là do số lượng các bản ghi mà bạn có thể chèn mỗi câu lệnh bằng 999 / # các cột. Bảng theo dõi có 9 cột, có nghĩa là mỗi cuộc gọi chèn theo lô cho db.execSQL () chỉ có thể chèn 111 bản ghi. Trong khi đó, trường hợp đơn giản chỉ có một cột - cho phép nạp 999 bản ghi cho mỗi lô.

Và các con số:

• one inserts: ~1400 track records per second.
• Batched inserts: ~1800 track records per second.

Sử dụng trực tiếp SQLiteStatement

Điều gì nếu, thay vì chỉ sử dụng các phương pháp có sẵn từ SQLiteDatabase, tôi đã thử sử dụng trực tiếp class SQLiteStatement? Việc suy nghĩ đã được theo cùng một đường như logic để đi từ insert () để execSQL (): cắt ra những "middle man" bất cứ nơi nào bạn có thể. ASQLiteStatement được sử dụng dưới các lớp phủ khi bạn gọi một trong hai phương thức đó, vì vậy sẽ có ý nghĩa nếu sử dụng đối tượng câu lệnh trực tiếp để có thể tăng tốc độ.

Ngoài ra, nếu chúng ta có thể tái sử dụng một đối tượng SQLiteStatement nhiều hơn nữa, chúng ta có thể thấy một số hiệu suất khác tăng do không phải tạo ra rất nhiều đối tượng.

Code cho insert một lần / từng cái một với SQLiteStatement:

SQLiteStatement stmt = db.compileStatement(
    "INSERT INTO inserts_1 (val) VALUES (?)"
);
db.beginTransaction();
for (int i = 0; i < numIterations; i++) {
    stmt.bindLong(1, random.nextInt());
    stmt.executeInsert();
    stmt.clearBindings();
}
db.setTransactionSuccessful();
db.endTransaction();

Mã cho chèn hàng loạt, sử dụng thủ thuật đệ quy từ các store statement trong bộ nhớ cache được lập chỉ mục theo kích thước dựa trên HashMap:

Map<Integer, SQLiteStatement> statementCache = new HashMap<>();

db.beginTransaction();
doInsertions(db, numInsertions, statementCache);
db.setTransactionSuccessful();
db.endTransaction();

// ... elsewhere in the class ...

void doInsertions(SQLiteDatabase db, int numInsertions, 
                  Map<Integer, SQLiteStatement> statementCache) {
    if (numInsertions > 999) {
        doInsertions(db, numInsertions - 999, statementCache);
        total = 999;
    }
    SQLiteStatement stmt;
    if (statementCache.containsKey(numInsertions)) {
        stmt = statementCache.get(numInsertions);
    } else {
        StringBuilder valuesBuilder = new StringBuilder();
        for (int i = 0; i < numInsertions; i++) {
            if (i != 0) {
                valuesBuilder.append(", ");
            }
            valuesBuilder.append("(?)");
        }
        stmt = db.compileStatement(
            "INSERT INTO inserts_1 (val) VALUES " 
            + valuesBuilder.toString()
        );
        statementCache.put(numInsertions, stmt);
    }

    for (int i = 0; i < numInsertions; i++) {
        stmt.bindLong(i+1, random.nextInt());
    }

    stmt.executeInsert();
    stmt.clearBindings();
}

Từ các biểu đồ, rõ ràng là việc sử dụng các đối tượng SQLiteStatement chèn một lần duy nhất không đánh bại batch insert sử dụng db.execSQL (). Tuy nhiên, việc áp dụng mô hình tái sử dụng các đối tượng câu lệnh vào thế giới của các chèn hàng loạt dường như cung cấp một chút cải thiện hiệu suất với chi phí của một số phức tạp thêm vào trong codebase.

Nguồn: https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-insertions-971aff98eef2