Iphone Sqlite3 Query too slow

Question

Hello I'm really stuck with my app. I need to make a simple SELECT on one of my tables with aprox 250.000 rows (50mb) using SQLITE3. When I load with Iphone Simulator the query takes 3 sec aprox. When i test my app on the Device the query takes 90 sec. Unfortunately I can't release my app with 90 sec of wait. Here i post my code:

-(void) loadResults {

sqlite3 *database;
NSMutableString *street;
zone = [[NSMutableArray alloc] init];

if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    const char *sqlStatement = [[NSString stringWithFormat:@"select street from streets "] UTF8String];
    sqlite3_stmt *compiledStatement;
    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
        while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

            street = [NSMutableString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
            [zone addObject:street];
        }
    }   
    sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);}   

This is how i've created my table using SQLITE3

CREATE TABLE streets (id INTEGER PRIMARY KEY, street TEXT, province TEXT, country TEXT, from TEXT, to TEXT, lat TEXT, lon TEXT);
CREATE INDEX strIndx on streets(street);

As you can see there is no WHERE statement, it is only a simple "SELECT street FROM streets"

Please I need help here Thanks in advance.

Answer 1

What are you trying to do? As you acknowledge you have no WHERE statement, I'm assuming you aren't doing anything like filtering the rows on the Obj-C side. Therefore the only thing I can think of that you are trying to do (please comment if I'm wrong) is load all of the rows for display. Apple has a very simple standard recommendation for this -- lazily loading the table. Basically, add a limit of, say, 15 rows to the original query, and have a way to retrieve more (typically this would be a table footer with blue text saying 'Load more...'). Alternatively this could be implemented by simply not loading the cells below the limit until requested (which will happen when the user scrolls to them).

Answer 2

Thank you for your answer Jared, what I need is to show all the streets in a UITableView and let the user filter the search in a UISearchBar or just let him select it from the list. I've changed my query to

SELECT DISTINCT(street) from streets

This returned a 3000 rows result but it takes the same amount of time as without the DISTINCT, that is 90 sec. I've also tried with "GROUP BY street", no benefits. However if I add .. LIMIT 3000 the result is instant. I imagine the query has to move through the whole table to get the DISTINCT values, which doesn't do with LIMIT, can it possible be a matter of the INDEX? How do I know if the index is working? Thanks again!

Answer 3

If you have 250,000 rows with only 3000 distinct results, each street is in the database 83 times on average.

Perhaps it would be appropriate to separate your streets into a separate, normalized table, with each value occurring only once, then reference those streets from your address table by ID. Then for your TableView, you could pull back just the values from your Streets table.

As was mentioned before, you may also have a UI paradigm issue. If you have a table with 3000 entries in it in the UI, that could potentially be very tedious for the user. (Imagine trying to scroll down one flick at a time to Yabar Street.)

You may want to at least lazy load the table, as was suggested before. Or maybe you can allow the user to type a street name, and provide a Google-suggest-type interface, popping up potential matches as they type.

Answer 4

My really doubt is, is it common a Table of 250000 rows to take 90 sec to query? If so, i shall end my project, if not, please help!

Answer 5

Anyone please? I had no luck with the previous answers, please help