Cursor Size Limit in Android SQLitedatabase

Cursor size limit in Android SQLiteDatabase

There's a limit of 1MB on internal assets due to dynamic decompression; the 1MB limit also seems to apply to Cursor blobs but this doesn't seem to be documented anywhere.

Generally you should avoid blobs in SQLite as they perform poorly; instead save the blob data as a file and store the location of the file in your DB.

Android how to query huge database in android (cursor size is limited to 1MB)

I found a way to handle this and I want to share with all who need it.

   int limit = 0;
   while (limit + 100 < numberOfRows) {
       //Compose the statement
       String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100";
       //Execute the query
       Cursor cursor = myDataBase.rawQuery(statement, null);
       while (cursor.moveToNext()) {
           Product product = new Product();
           product.setAllValuesFromCursor(cursor);
           productsArrayList.add(product);
      }
      cursor.close();
      limit += 100;
 }

 //Compose the statement
 String statement = "SELECT * FROM Table ORDER someField LIMIT '"+  (numberOfRows - limit)+"', 100";
 //Execute the query
 Cursor cursor = myDataBase.rawQuery(statement, null);

 while (cursor.moveToNext()) {
     Product product = new Product();
     product.setAllValuesFromCursor(cursor);
     productsArrayList.add(product);
 }
 cursor.close();

The main idea is to split your data, so you can use the cursor as it should be used. It's working under 2 s for 5k rows if you have indexed table.

Thanks,
Arkde

SQLite CursorWindow limit - How to avoid crash

CursorWindow size limit is 2MB (as of now). You cannot read a single row whose size exceeds 2MB because it is not possible to put that in a Cursor.

So instead of storing the entire JSON as a single element, you can parse it and store in separate columns or tables in the database.

So that,

1. You can leave the unwanted data in the JSON from saving in the database.
2. You can query a part of the data (few columns) at a time so that the queried data will not cross the 2MB CursorWindow limit.

Or you can try out other Database systems, like Realm (I haven't tried it, so I'm not sure if there is any limit there).

sqlite get field with more than 2 MB

It is not advisable to store large BLOBS or similar data in the SQLite database. You should use the file system and only store a reference to the data in you database.
Refer to this answer

---

Related Topics