title: Android Sqlite Performance
body: <p>I have been doing some experiments to measure sqlite performance on android. I was disappointed a little bit with the results. What i did was inserting 10.000  queries to table and <strong>it took 130-140 seconds</strong> <em>but with these conditions</em>;</p>
  
  <p><strong>1.</strong> Samsung galaxy s3 in power saving mode </p>
  
  <p><strong>2.</strong> Inserted data(or class)has 3 strings and one float(real for sqlite)</p>
  
  <p><strong>3.</strong> Insert event is being done in asynctask.</p>
  
  <p><strong>4.</strong> In asynctask, i am showing a progress dialog with passed timer text in it (System.currentTimeMillis - seconds etc blala)</p>
  
  <pre><code>class AddStudentsTask extends AsyncTask&lt;Void,Integer,Void&gt;
  {
      ProgressDialog prgDialog;
      int max = 10000;
      Student s;
      long seconds = System.currentTimeMillis();
  
  
      @Override
      protected void onPreExecute() {
          super.onPreExecute();
          prgDialog = new ProgressDialog(MainActivity.this);
          prgDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
          prgDialog.setMessage(seconds+"");
          prgDialog.setMax(max);
          prgDialog.setCancelable(false);
          prgDialog.show();
  
      }
  
      @Override
      protected void onProgressUpdate(Integer... values) {
          super.onProgressUpdate();
          prgDialog.setProgress(values[0]);
          sList.add(s);
          String s = (System.currentTimeMillis()-seconds)/100+"";
          if(s.length()&gt;2)
              s = s.substring(0,s.length()-1) + "." + s.charAt(s.length()-1);
          else if(s.length() == 2)
              s = s.charAt(0) + "." + s.charAt(1);
          prgDialog.setMessage(s + " seconds passed.");
  
      }
  
      @Override
      protected Void doInBackground(Void... voids) {
  
          for(int a = 0;a&lt; max; a++ )
          {
              Random r = new Random();
              s = new Student();
  
              s.setGpa(r.nextFloat()*4);
              s.setLastName("asdasd");
              s.setFirstName("Oh My God");
              s.setAddress("1sadasd");
              s.setId(sda.insert(s));
              publishProgress(a);
          }
  
          return null;
      }
  
      @Override
      protected void onPostExecute(Void aVoid) {
          super.onPostExecute(aVoid);
          prgDialog.dismiss();
          sa.notifyDataSetChanged();
      }
  }
  </code></pre>
  
  <p><strong>5.</strong> I am using contentValues with insertOrThrow method in helperdb class.
  <strong>THIS IS OLD SLOW CODE</strong></p>
  
  <pre><code>public long insert(Student s)
  {
      SQLiteDatabase db = sh.getWritableDatabase();
      ContentValues cv = new ContentValues();
      cv.put(StudentHelper.FIRSTNAME,s.getFirstName());
      cv.put(StudentHelper.LASTNAME,s.getLastName());
      cv.put(StudentHelper.ADDRESS,s.getAddress());
      cv.put(StudentHelper.GPA,s.getGpa());
      s.setId(db.insertOrThrow(StudentHelper.TABLE_NAME, null, cv));
      return s.getId();
  }
  </code></pre>
  
  <p><strong>6.</strong> This task is done in onCreate method of activity.</p>
  
  <p><em>So what am i doing wrong here or expecting too much from it? Are these results ok or bad?</em> </p>
  
  <p><strong>What can i do to improve my code?</strong></p>
  
  <p><strong>EDIT</strong></p>
  
  <p>So i changed my insert code to this and it reduced to 4.5 seconds!!!</p>
  
  <pre><code>public ArrayList&lt;Long&gt; insertMany(ArrayList&lt;Student&gt; stus)
  {
      ArrayList&lt;Long&gt; ids = new ArrayList();
      String sql = "INSERT INTO "+StudentHelper.TABLE_NAME+"" +
              "("+StudentHelper.FIRSTNAME+","+StudentHelper.LASTNAME+"," +
              " "+StudentHelper.GPA+") values(?,?,?)";
      SQLiteDatabase db = sh.getWritableDatabase();
      db.beginTransaction();
  
      for(Student s:stus) {
          SQLiteStatement stmt = db.compileStatement(sql);
  
          stmt.bindString(1, s.getFirstName());
          stmt.bindString(2, s.getLastName());
          stmt.bindDouble(3, s.getGpa());
  
          s.setId(stmt.executeInsert());
          ids.add(s.getId());
          stmt.clearBindings();
      }
  
      db.setTransactionSuccessful();
      db.endTransaction();
  
      return ids;
  }
  </code></pre>