CODEWITHSUNDEEP
androidsqlperformance
Philderbeast
Philderbeast

Reputation: 71

Android SQLite taking a long time to complete

I am working on building an android app but I'm nothing that my SQL operations are taking forever to complete

I have profiled the app to narrow it down to the act of moving the Cursor to the first row of the result set for a simple select query (only returning 1 row where I'm testing it to eliminate the size of the result set as a problem.

Are there any better options for moving to the start of the result set?

edit:

some code:

public Cursor sqlQuery(String Sql)
{
    Cursor c = database.rawQuery(Sql, null);
    //start at the first spot
    c.moveToFirst();
    return c;
}

and the calling code

    Cursor Slots = evedb.sqlQuery("SELECT attrib.value, "
            +"attribtypes.attributename "
            +"FROM dgmTypeAttribs AS attrib "
            +"INNER JOIN invTypes AS type "
            +"ON attrib.typeID = type._ID "
            +"INNER JOIN dgmAttribs AS attribtypes "
            +"ON attrib.attributeID = attribtypes._ID "
            +"AND attribtypes.attributename = 'hiSlots'  "
            +"AND type.typeName = '" + shipname + "'");

this runs 3 times and i have time it to approximately 2 secs for all the rest of the code or over a minute with this in.

i have also run just the rawQuery statement with out the move to first and its takes bout 18 secs so im looking at the worst part of the code first.

Upvotes: 1

Views: 2753

Answers (2)

piotrpo
piotrpo

Reputation: 12636

You probably, have large datasets. What you can do:

  1. Ensure you have proper indexes (all your FK fields are indexed? have you set PK on your _ID fields for every table?) EVERY field used in ON, or WHERE syntax should be indexed (generally speaking).

  2. You should change a bit your query:

    +"ON attrib.attributeID = attribtypes._ID "
+"WHERE attribtypes.attributename = 'hiSlots'  "
+"AND type.typeName = '" + shipname + "'");

  3. Consider using PreparedStatement

  4. Check amount of data used by this query - remove where condition and just put count(*)

  5. Consider to split one big query into few smaller. There is also EXPLAIN command which can be used to check way used by SQLite to retrieve data. Look here to get more information.

Moving to first row is the point when query is starting to collect data so this is just database performance issue.

Upvotes: 1

Scoobler
Scoobler

Reputation: 9719

Have you got the snipet of code you are currently using to see what is possibly going wrong?

If you are talking about moving to a first item in a cursor I use: c.moveToFirst() where c is the cursor.

Upvotes: 0

Related Questions