vendredi 11 décembre 2015

SQLite database queries very slow (SQLite Asset Helper)

I'm working on a app where saxophone players can choose a saxophone (soprano, alto, tenor or baritone). The database should output all the music pieces that are available for that specific saxophone, together with the composer and publisher. I've used a prepopulated db (created with SQLite Manager) and used the SQLite Asset Helper. The database (so far) has some 600 records. Whenever I query the database (let's say on 'alto'), it takes some 10 seconds before the results are found. I've tried many things: indexing (both in the db file as in the Java code), changed between sqlite-file and db-file, tried on a real device instead of emulator etc, etc. Nothing seems to work... I hope someone out there can help me on this...

I use two java classes: MyDatabase and SQLiteOpenHelper. If you need more info, please let me know.

MyDatabase class:

    package com.example.musicrepertoiredatabase2;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class MyDatabase extends SQLiteAssetHelper {

    SqliteAssetHelper helper = new SqliteAssetHelper();

    private static final String DATABASE_NAME = "saxophone_repertoire.sqlite";
    private static final String TABLE_NAME = "repertoire";
    private static final int DATABASE_VERSION = 1;

    private static final String ID = "id";
    private static final String COMPOSER = "composer";
    private static final String TITLE = "title";
    private static final String PUBLISHER = "publisher";
    private static final String SAXOPHONE = "saxophone";
    private static final String ACCOMPANIMENT = "form";

    public MyDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public Cursor getData() {
        SQLiteDatabase db = this.getWritableDatabase();

        String saxSort = SqliteAssetHelper.chooseSax;
        String composer = SqliteAssetHelper.chooseComposer;

        Cursor result = db.rawQuery("SELECT Composer, id FROM " + TABLE_NAME
                + " WHERE " + SAXOPHONE + "='" + saxSort + "'", null);

        return result;

    }
}

My SQLiteAssetHelper class:

    package com.example.musicrepertoiredatabase2;

import android.app.ProgressDialog;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Handler;
import android.support.v7.app.ActionBarActivity;
import android.text.Html;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class SqliteAssetHelper extends ActionBarActivity implements
        OnItemSelectedListener, OnClickListener {

    TextView composer, title, saxophone, accompaniment, showInfo, count;
    EditText etComposer, etTitle;
    Spinner spinnerSaxophone, spinnerAccompaniment;
    Button search, clear;
    String[] arraySaxophone = { "Soprano", "Alto", "Tenor", "Baritone" };
    String[] arrayAccompaniment = { "Solo", "Piano" };
    public static String chooseSax = null;
    public static String chooseComposer = null;
    Handler updateBarHandler;

    private MyDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        updateBarHandler = new Handler();

        spinnerSaxophone = (Spinner) findViewById(R.id.spinner_saxophone);
        spinnerSaxophone.setOnItemSelectedListener(this);
        ArrayAdapter<String> adapter1 = new ArrayAdapter<String>(this,
                android.R.layout.simple_spinner_item, arraySaxophone);
        spinnerSaxophone.setAdapter(adapter1);

        spinnerAccompaniment = (Spinner) findViewById(R.id.spinner_accompaniment);
        spinnerAccompaniment.setOnItemSelectedListener(this);
        ArrayAdapter<String> adapter2 = new ArrayAdapter<String>(this,
                android.R.layout.simple_spinner_item, arrayAccompaniment);
        spinnerAccompaniment.setAdapter(adapter2);

        etComposer = (EditText) findViewById(R.id.et_composer);
        etComposer.setHint(Html.fromHtml("<small>"
                + getString(R.string.hint_composer) + "<small>"));
        etTitle = (EditText) findViewById(R.id.et_title);
        etTitle.setHint(Html.fromHtml("<small>"
                + getString(R.string.hint_title) + "<small>"));
        search = (Button) findViewById(R.id.btn_search);
        clear = (Button) findViewById(R.id.btn_clear);
        search.setOnClickListener(this);
        clear.setOnClickListener(this);
        showInfo = (TextView) findViewById(R.id.tv_showinfo);
        count = (TextView) findViewById(R.id.tv_count);

        db = new MyDatabase(this);

    }

    public void launchRingDialog() {
        final ProgressDialog ringProgressDialog = ProgressDialog.show(this,
                "Searching database...", "Please wait...", true);

        ringProgressDialog.setCancelable(true);

        new Thread(new Runnable(){

            @Override
            public void run(){
                try{
                    Thread.sleep(5000);
                }catch (Exception e){

                }

                ringProgressDialog.dismiss();
            }
        }).start();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }
        return super.onOptionsItemSelected(item);
    }

    @Override
    public void onItemSelected(AdapterView<?> parent, View view, int position,
            long id) {

        int choosePositionSax = spinnerSaxophone.getSelectedItemPosition();

        switch (choosePositionSax) {
        case 0:
            chooseSax = "Soprano";
            break;
        case 1:
            chooseSax = "Alto";
            break;
        case 2:
            chooseSax = "Tenor";
            break;
        case 3:
            chooseSax = "Baritone";
            break;

        default:
            break;
        }
    }

    @Override
    public void onNothingSelected(AdapterView<?> parent) {

    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
        case R.id.btn_search:

            launchRingDialog();
            chooseComposer = etComposer.getText().toString();
            Cursor result = db.getData();

            if (result.getCount() == 0) {
                Toast.makeText(this, "Nothing found", Toast.LENGTH_SHORT)
                        .show();
            }
            launchRingDialog();
            StringBuffer buffer = new StringBuffer();
            while (result.moveToNext()) {
                //buffer.append("Title: " + result.getString(2) + "\n");
                buffer.append("Composer: " + result.getString(1) + "\n\n");

                showInfo.setText(buffer);

                int countFiles = result.getCount();
                count.setText(Integer.toString(countFiles));

            }
            break;
        case R.id.btn_clear:
            showInfo.setText("");
            etComposer.setText("");
            etComposer.requestFocus();
            break;
        default:
            break;
        }
    }

}

Aucun commentaire:

Enregistrer un commentaire