Android - What is the Best/Efficient way of converting an Encrypted database to a plain database file

Question

I have used SQLiteCipher to add security to my application database, the problem is, it is way too slow than the default android SQLite API. So I switched back to the default SQLite API, but I really need the security provided by SQLCipher, so What I did is:

Upon opening my app

1. Open the encrypted database file
2. Create a normal database file
3. Transfer records from encrypted to normal database
4. Delete the encrypted database file
5. Use the normal database file

When my app is closed,

1. Open the normal database file
2. Create a encrypted database file
3. Transfer records from normal to encrypted database
4. Delete the normal database file

This works without any problems, but transferring records takes some time and will consume more(I am assuming that my database file will contain thousands of records during its real-life usage). So is there any other way of what I have done? Are there any more efficient ways of doing this? TIA!

EDIT : Here are some code using the SQLCipher

Main Activity

private final static String phrase = "passW0rd3r";

private EditText            StudNum, StudName, StudCrse;
private DBHelper            dbIns;
SQLiteDatabase              DBFile;
private MainDBHelper        DBHelp;
private MenuItem            msg_app;
final Context               con = this;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    StudNum = (EditText)findViewById(R.id.txtsNum);
    StudName = (EditText)findViewById(R.id.txtsName);
    StudCrse = (EditText)findViewById(R.id.txtCourse);
    StudNum.requestFocus();

    SQLiteDatabase.loadLibs(this);
    dbIns = new DBHelper(this, DB_NAME);
    DBFile = dbIns.getWritableDatabase(phrase);
    DBHelp = new MainDBHelper(this);
//Takes too much time to load, loads around 3 seconds, compared to
//the default SQLite API, which is half a second only
}

MainDBHelper class

    public class MainDBHelper {

    private static final String DB_NAME = "StudentInfo.db";
    private static final String TABLE_NAME = "StudentInfo";
    private final static String phrase = "passW0rd3r";

    private DBHelper openHelper;
    private SQLiteDatabase database;

    public MainDBHelper(Context context) {
        openHelper = new DBHelper(context, DB_NAME);
        database = openHelper.getWritableDatabase(phrase);
    }
}

DBHelper class

public class DBHelper extends SQLiteOpenHelper{

    private String DBName;
    public DBHelper(Context context, String DBname) {
        super(context, DBname, null, 5);
        this.DBName = DBname.substring(0,DBname.length()-3);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        // TODO Auto-generated method stub
        arg0.execSQL("CREATE TABLE " + DBName + " (_id INTEGER PRIMARY KEY, " +
                "Stud_Num TEXT, Stud_Name TEXT, Stud_Crse TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub
        arg0.execSQL("DROP TABLE IF EXISTS " + DBName);
        onCreate(arg0);
    }
}

Extra class that uses the same Database Connection

DisplayMessageActivity class - this one loads around 2 seconds just to show 3 records in the list view. If I use the default SQLite API, it can load up to 10 records in just a second

public class DisplayMessageActivity extends Activity {

        private ListAdapter             listAdapt;
        private MainDBHelper            DBHelp;

        private EditText                edtName, edtSnum, edtCrse;
        private TextView                dName, dSnum, dCrse, ssn;
        private ListView                lst_snum;
        private SearchView              sView;
        private MenuItem                sItem;

        private View                    lView, diagView;
        private AlertDialog.Builder     DBuilder;
        private AlertDialog             ADiag;
        private String                  StudentNumber, edSname, edSnum, edCrse;


        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_display_message);
            DBHelp = new MainDBHelper(this);

            lst_snum = (ListView)findViewById(R.id.lv_test);

            refreshList();
            createADiag(this);

            lst_snum.setOnItemLongClickListener(new OnItemLongClickListener(){
                @Override
                public boolean onItemLongClick(AdapterView<?> parent, View view,
                        int position, long id) {
                    // TODO Auto-generated method stub
                    lView = view;
                    ssn = (TextView)view.findViewById(R.id.studNum);
                    ADiag.show();
                    return false;
                }
            });
        }

        private void refreshList(){
            listAdapt = null;
            listAdapt = new ListAdapter(this, DBHelp.getTimeRecordList());
            lst_snum.setAdapter(listAdapt);
        }
    }

Update: I've read some questions related to mine and I really see that some are also facing performance issues by using SQLCipher, one answer said that to "Cache the database" for one time connection only that can be used on multiple activities on the same app, problem is, I don't have any idea to do that

Answer 1

There are a few very important guidelines for optimal SQLCipher performance:

• Do not repeatedly open and close connections, as key derivation is very expensive, by design
• Use transactions to wrap insert / update / delete operations. Unless executed in a transaction scope, every operation will occur within it's own transaction which slows things down by several orders of magnitude
• Ensure your data is normalized (i.e., using good practices for separation of data into multiple tables to eliminate redundancy). Unnecessary duplication of data leads to database bloat, which means more pages for SQLCipher to operate on
• Ensure that any columns that are used for searches or join conditions are indexed. If you don't, SQLCipher will need to execute full database scans across large numbers of pages
• Vacuum periodically to ensure databases are compact if you do large deletes, updates etc.

Finally, to diagnose further the performance of your specific query statements, there are a couple of options. First, I would recommend running PRAGMA cipher_profile as CommonsWare mentioned above, you can read more about the usage here. This will give you a log of the queries performed on the database and their respective execution times in milliseconds. Next, you run an explain query plan command against some of your queries that may be performing poorly? The output of the explain query command is described here.