slow freeze while deleting 30K rows

[Arie]

Hi,

For an app of a customer of mine I use FMDB in combination with SQLCipher. One of the new features is downloading content packages and being able to remove them as well.

A package consists of approximately 30K rows (downloaded as a JSON file). I am debugging the app on an iPhone 6.

Inserting a package will take about 10 seconds. That's quite long, but I am able to run this on a background thread, so the UI does not freeze. But, when I delete these rows, the UI freezes during the 3 seconds it takes, even using another thread. I use transactions for these inserts and deletes (calling the inTransaction method of FMDB). 

My main question is: how can I avoid freezing the UI?

But I am also interested in how to speed up these transactions.

For completeness I attach the code for deletion:

ViewController:

if (editingStyle == UITableViewCellEditingStyleDelete) {

       PackageClass * packageToDelete = [packages objectAtIndex: indexPath.row];

        dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0), ^{

           [[databaseManager sharedDatabaseManager] deletePackageWithID: packageToDelete.ID];

           

           dispatch_async(dispatch_get_main_queue(), ^{

               packageToDelete.installed = NO;
               [tableView reloadRowsAtIndexPaths:[NSArray arrayWithObject:indexPath]

                                withRowAnimation:UITableViewRowAnimationAutomatic];

           });

       });

       

       [tableView reloadRowsAtIndexPaths:[NSArray arrayWithObject:indexPath]

                        withRowAnimation:UITableViewRowAnimationAutomatic];

       

        NSLog(@"Main queue proceeds");

   }

DatabaseManager:

- (void)deletePackageWithID:(int)ID {

       [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {

              [db executeUpdateWithFormat:@"DELETE FROM packageRows WHERE id = %d", ID];

       }];

}

Thank you in advance for your help!

Arie

[Nick Parker]

Hello Arie,

Could you try aggregating the id's in which you wish to delete and executing a single query within a transaction like this:

DELETE FROM packageRows WHERE id IN (?, ?, ?, ?...);

This would allow you to execute all delete's from a single SQL statement.

• (void)deletePackageWithID:(int)ID {

• [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {

     [db executeUpdateWithFormat:@"DELETE FROM packageRows WHERE
  

  id = %d", ID];

  }];

}

Thank you in advance for your help!

Arie

--

---

You received this message because you are subscribed to the Google Groups "SQLCipher Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Nick Parker

[Stephen Lombardo]

Hello Arie,

One other quick comment, can you confirm that the packageRows id column is indexed?

In your case it might also be worthwhile to try WAL mode. Since you have separate threads, with WAL, one could be reading while inserts / deletes are running.

Cheers,

Stephen

 

--

Stephen Lombardo | Zetetic LLC | +1-908-229-7312 | sjlom...@zetetic.net

--

---