Asked  6 Months ago    Answers:  5   Viewed   36 times

I need to parse a fairly large XML file (varying between about a hundred kilobytes and several hundred kilobytes), which I'm doing using Xml#parse(String, ContentHandler). I'm currently testing this with a 152KB file.

During parsing, I also insert the data in an SQLite database using calls similar to the following: getWritableDatabase().insert(TABLE_NAME, "_id", values). All of this together takes about 80 seconds for the 152KB test file (which comes down to inserting roughly 200 rows).

When I comment out all insert statements (but leave in everything else, such as creating ContentValues etc.) the same file takes only 23 seconds.

Is it normal for the database operations to have such a big overhead? Can I do anything about that?

 Answers

39

You should do batch inserts.

Pseudocode:

db.beginTransaction();
for (entry : listOfEntries) {
    db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

That increased the speed of inserts in my apps extremely.

Update:
@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database

Tuesday, June 1, 2021
 
mattltm
answered 6 Months ago
53

Constraint failed usually indicates that you did something like pass a null value into a column that you declare as not null when you create your table.

Friday, July 30, 2021
 
ajaybc
answered 4 Months ago
65

This is a typical issue with not using the selectionArgs, and it is exactly why one should use them: You are quoting your string with simple quotes, but your string contains single quote, so SQLite detect the end of the string before it actually ends, and try to parse the rest as SQLite keyword.

The proper solution is to leave the escaping to SQLite, by using those selectionArgs rather that trying to do the escaping your self. in you case, that would be :

Cursor cursor = ourDatabase.rawQuery("select 1 from "
            + DBHelper.DATABASE_TABLE + " where " + DBHelper.TITLE + "=?"
            + " AND " + DBHelper.DATE + "=?",
            new String[] {title, date});

plus, it's cleaner because you can make a constant out of your query rather than constructing it every time.

Wednesday, August 18, 2021
 
Michal Charemza
answered 4 Months ago
100

Solution

To open such databases* with sqlite-browser, you need to copy all three files. All must be in the same directory.

* Databases stored in multiple files as stated in the question.


Why three files?

As per docs, Starting from version 1.1.0, Room uses write-ahead logging as default journal mode for devices which has sufficient RAM and running on API Level 16 or higher. It was Truncate for all devices until this version. write-ahead logging has different internal structure compared to Truncate.


Take a look at the files temporary files used by SQLite now and then :

Until version 1.1.0

enter image description here

From version 1.1.0

enter image description here


If you want to change the journal mode explicitly to Truncate, you can do it this way. But, it is not recommended because WAL is much better compared to Truncate.

public static void initialize(Context context) {
    sAppDatabase = Room.databaseBuilder(
            context,
            AppDatabase.class,
            DATABASE_NAME)
        .setJournalMode(JournalMode.TRUNCATE).build();
}


Is it possible to move it to single file without changing to Truncate ?

Yes, it is. Query the following statement against the database.

pragma wal_checkpoint(full)

It is discussed in detail here here.

Tuesday, September 14, 2021
 
Loomer
answered 3 Months ago
87

In order to upgrade your database you need to do properly by manipulating the schema I found Both the answers for my questions were useful...

Upgrading database version automatically

Wednesday, November 10, 2021
 
Justin
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share