fast read of blobs within SQLite using simpleQuery

问题:

I am reading blobs with size between 100kb and 1000kb from SQlite in my Android App using the following code :

public Object getCachefromDb(String sIdentifier){

    String sSQL = " Select cache from cachtable where identifier='" + sIdentifier + "'";

    Cursor c = null;
    try {
        c = connection_chronica.rawQuery(sSQL, null);
    } catch (SQLiteException e) {
        Log.v("SQLite Excetion", e.getMessage());
    }


    c.moveToFirst();
    Log.v("DEBUG load Cache","sIdentifier : " + sIdentifier);
    byte[] bData=null;
    try{
        bData = c.getBlob(0);
    }catch(Exception e){
        e.printStackTrace();
    }
    Object o = null;

    if (bData!=null){
        ByteArrayInputStream bos = new ByteArrayInputStream(bData);
        ObjectInputStream ois;
        try {
            ois = new ObjectInputStream(bos);
            o=ois.readObject();

        } catch (StreamCorruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    c.close();
    return o;

}

I would like to optimize the speed of reading and I found articles mentoining simpleQueryForBlobFileDescriptor.

My question : Does this help me reading BLOBS faster ? and if so how can I use it ?

Example from other posts:

SQLiteStatement get = mDb.compileStatement(
    "SELECT blobColumn" + 
    " FROM tableName" +
    " WHERE _id = 1" +
    " LIMIT 1"
);

ParcelFileDescriptor result = get.simpleQueryForBlobFileDescriptor();
FileInputStream fis = new FileInputStream(result.getFileDescriptor()); // read like any other

回答1:

My Tests results says its slower.

After long testing I found out, that using simpleQueryForBlobFileDescriptor is slower. See following code. My old code for example reads a blob in 390 miliseconds and the new code with simpleQueryForBlobFileDescriptor reads the same blob in 805 ms. I read somewhere that simpleQueryForBlobFileDescriptor should be very fast for blob readings, but this seems not to be in my tests. Perhabs I am not doing it properly ? ( I hope so ). Any other hints.

public Object getCachefromDb_old(String sIdentifier){

    Log.v("DEBUG LOAD BLOB","Start : " + sIdentifier);
    Object o = null;
    try {
        // Erstelle ein SQLStatemant mit einer InClause
        String sSQL = " Select cache from cachetable where identifier='" + sIdentifier + "'";
        SQLiteStatement get = connection_chronica.compileStatement(sSQL);
        ParcelFileDescriptor result = get.simpleQueryForBlobFileDescriptor();
        FileInputStream fis = new FileInputStream(result.getFileDescriptor());
        ObjectInputStream inStream = new ObjectInputStream(fis);
        o=inStream.readObject();

    } catch (StreamCorruptedException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    } catch (IOException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    Log.v("DEBUG LOAD BLOB","End : " + sIdentifier);

    return o;

}