Getting values from multi-dimensional array too slow

Question

Good Day, I've been working on an update in an android project, and came across an issue. I have to read questions from an SQLite database which i've done successfully by loading it into a multi-dimensional array as shown below in my database helper class:

public String getSome(int s,int t, String Table_Name){

        String selectQuery = "SELECT  * FROM " + Table_Name;
              SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        int rows = cursor.getCount();
        int num=0;
        int col = 0;
        String[][] base = new String[rows][13]; 

        if (cursor.moveToFirst()) {
            do {
                for (col=0;col<13;++col ){
                base[num][col] = (cursor.getString(col));} 
                ++num;
            } while (cursor.moveToNext());

            return base[s][t];
        }



        return null;

    }

With that done, i read the questions as such in my question class:

public void database_calls(){
          setCourseTag(courseTag);
          myDbHelper = new DataBaseHelper(getActivity());


            try {
                myDbHelper.createDataBase();
            } catch (IOException ioe) {
            throw new Error("Unable to create database");
            }

            try {
                myDbHelper.openDataBase();
            }catch(SQLException sqle){
                throw sqle;
            }

            String no= myDbHelper.getSome(ques,0, getCourseTag());
            String qu = myDbHelper.getSome(ques,1, getCourseTag());
            String a = myDbHelper.getSome(ques,2, getCourseTag());
            String b = myDbHelper.getSome(ques,3, getCourseTag());
            String c = myDbHelper.getSome(ques,4, getCourseTag());
            String d = myDbHelper.getSome(ques,5, getCourseTag());
            ans = myDbHelper.getSome(ques,6, getCourseTag());
            img = Integer.parseInt(myDbHelper.getSome(ques,8, getCourseTag()));
            exp = myDbHelper.getSome(ques,9, getCourseTag());
            year = myDbHelper.getSome(ques,10, getCourseTag());
            questionImage = myDbHelper.getSome(ques,11, getCourseTag());
            length = myDbHelper.getMax(getCourseTag());
        }

So recently, i tried to use the year column, (i.e, column 10) to qualify the questions chosen for each quiz session, so that the user may be able to select the questions from any year, he/she wants to attempt. In order to do this, i used a loop at the beginning of the activity to filter out only the required year past questions. Then i transferred the indices of each question to a set, bal , from where it is iterated and so on..

public void countYearQuestions(){

        for(int y = 0; y < length; ++y){
            //year = myDbHelper.getSome(y,10, getCourseTag()); 

            if (selectedYear.equals(myDbHelper.getSome(y,10, getCourseTag())))
              bal.add(y);
        }
    }

Here, length is the size of the entire question database, for the course, (indicated by getCourseTag()). The code works quite alright. But it takes a whole 8-9secs!! for the activity to load. Any help on how to reduce this loading time would be appreciated.

Why the array? Why don't you let the database do the filtering for you? — CL.
– CL., Commented Jun 10, 2015 at 16:26
@CL. omg, i didn't think of that. So in essence, i can simply do the filtering from the sqlite db, in the DB helper class. I believe that should speed up the process, right? — Mofe Ejegi
– Mofe Ejegi, Commented Jun 10, 2015 at 23:29

maaartinus · Accepted Answer · 2015-06-11 03:29:17Z

1

The way you're doing this is pretty efficient in slowing everything down (and your helper helps you with it a lot):

String no= myDbHelper.getSome(ques,0, getCourseTag());

In each such line you execute a query, create a 2D array holding the whole table and throw nearly everything away. And you fail to close the Cursor.

So you need 12 values from a table and read instead the whole table 12 times.

answered Jun 11, 2015 at 3:29

maaartinus

46.8k40 gold badges176 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mofe Ejegi Over a year ago

Thanks for the eye-opener, my first solution to this would be to make the getSome() method create a single dimension array (1D) of the required column. I believe that if i do so, the entire table won't be read 12 times, but rather, each column would, thereby making the table read only once. Am I right on this?

Mofe Ejegi Over a year ago

Tried it and as expected, everything sped up, including all question refreshes. So, any more tips on how to improve the performance, seeing as loading time is reduced to 3 seconds, which I still feel can be less.

maaartinus Over a year ago

I'd drop the helper and use a local variable for getCourseTag (no idea how expensive this call is). If you need all columns from a single row, that's a single query (SELECT ... WHERE ..) and should take a few milliseconds at most.

Mofe Ejegi Over a year ago

Really sorry, I lost you at the (SELECT ... WHERE ..) part, my sqlite knowledge ain't that vast. I intend on getting all rows in a single column, how can i use the sqlite commands to execute this, replacing the 1D array?

maaartinus Over a year ago

@Mofe-hendyEjegi Single column and all rows? That's strange, thing about transposing it. Anyway, you do do it with SELECT theWantedColumnName FROM tableName without any 2D array.

|

Collectives™ on Stack Overflow

Getting values from multi-dimensional array too slow

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related