mercredi 20 mai 2015

iOS - Searching a SQLite database is slow

Hi I have built a dictionary application my data base has 78.000 record , so when I trying to search through data base the searching process is not smooth and you feel some delay ! specially when try to type first letter when app lunched . here is my codes , I would be grateful if help me to find a solution :

Searching :

  - (void)searchWord:(NSString *)txt{

        sqlite3_exec(database, "REINDEX table;", NULL, NULL, NULL);

    NSMutableArray *DB_Array = [[NSMutableArray alloc] init];

    NSString *dbPath = [[NSString alloc] initWithString: [self getDBPath]];

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

//      @"SELECT * FROM DIC WHERE NAME !=  '%@%%' ORDER BY NAME LIMIT 30"
//      SELECT * FROM DIC  Where Name LIKE '%@%%' order by NAME LIMIT 30

        NSString *sql =[NSString stringWithFormat:@"SELECT ID,NAME FROM DIC WHERE NAME LIKE  '%@%%' ORDER BY NAME LIMIT 15",txt];
        sqlite3_stmt *compiledStatement;

        if(sqlite3_prepare_v2(database, [sql UTF8String] , -1, &compiledStatement, NULL) == SQLITE_OK) {
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

                NSInteger oid = sqlite3_column_int(compiledStatement, 0);

                const char* f1 = (const char*)sqlite3_column_text(compiledStatement, 1);
                NSString *oName = f1 == NULL ? nil : [[NSString alloc] initWithUTF8String:f1];

//                NSLog(@"%@",oName);

                const char* f2 = (const char*)sqlite3_column_text(compiledStatement, 2);
                NSString *oMean = f2 == NULL ? nil : [[NSString alloc] initWithUTF8String:f2];


                const char* f3 = (const char*)sqlite3_column_text(compiledStatement, 3);
                NSString *oPron = f3 == NULL ? nil : [[NSString alloc] initWithUTF8String:f3];

                NSInteger bm = sqlite3_column_int(compiledStatement, 5);

                readerClass = [[Reader alloc]initWithReadDB:oid Name:oName Mean:oMean Pron:oPron bookMark:bm];

                [DB_Array addObject:readerClass];

            }
        }
        else {
            NSLog(@"Error retrieving data from database.");
        }
        sqlite3_close(database);
    }
    else {

        NSLog(@"Error: Can't open database!");
        NSLog(@" DB Name %@",viewController.dbName);
    }

    AppDelegate *appDelegateClass = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    [appDelegateClass.wordList removeAllObjects];
    [appDelegateClass.wordList=DB_Array mutableCopy];
    [appDelegateClass setCurrentDBPath:[dbPath UTF8String]];

}

TableView :

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    appClass = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    NSLog(@"%d",appClass.wordList.count);
    return  appClass.wordList.count;

}


- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

    static NSString *CellIdentifier = @"Cell";

     UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
    }

    appClass = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    readerClass = (Reader *)[appClass.wordList objectAtIndex:indexPath.row];
    cell.textLabel.text  = readerClass.Name;


    return cell;
}

SearchBar :

- (void)searchBar:(UISearchBar *)theSearchBar textDidChange:(NSString *)searchText {

    if([searchText length] > 0) {

        dbClass=[[DB alloc]init];
        [dbClass searchWord:searchText];

    }else

    {
        dbClass=[[DB alloc]init];
        [dbClass searchWord:@" "];
    }

    [self.myTable reloadData];

}

Aucun commentaire:

Enregistrer un commentaire