Denis Kutlubaev

Tag: SQL

  • SQLite FMDB Batch / Bulk Inserts

    SQLite
    I use FMDB framework to work with SQLite database on iOS. Sometimes you need to insert many rows, many objects that come from a server to your local database. It can take some time. When I was inserting rows one by one, the method took 50 seconds to finish inserting about 1300 rows. So I decided to find a faster solution.

    And found it here. To insert many rows faster you should use transactions. It is very easy, you shouldn’t use complex approaches, like preparing huge SQL statements. I got 0.5 seconds as a result on iPhone 3GS. Here is more sophisticated approach to optimize insert of millions of rows, but it’s not my case, I’m satistfied with 0.5 second result.

    _72483466_clockthinkstock

    This is a sample code: 

    + (BOOL)addToDbRegionArray:(NSArray *)regionArray
{
  FMDatabase *db = [[DBConnection sharedInstance] db];
  
  if ( ! [db open] ) {
    return NO;
  }
  
  [db beginTransaction];
  
  for (RRRegion *region in regionArray) {
    [db executeUpdate:@"INSERT INTO regions (region_id, name, city_id, country_id, has_metro, is_popular) VALUES (?, ?, ?, ?, ?, ?);" withArgumentsInArray:@[@(region.sid), region.name, @(region.cityId), @(region.countryId), @(region.hasMetro), @(region.isPopular)]];
  }
  
  [db commit];
  [db close];
  
  return YES;
}

    I have a class RRRegion, which objects I am inserting. I open database, then begin a transaction, execute as many updates as I have objects and commit a transaction, close database.

    I should mention, that when I was using caching of statements by FMDB, I got strange leak statement errors, so I don’t cache them. I don’t have this line: 

    [db setShouldCacheStatements:NO];

    This is how I measured the time it takes: 

    NSDate *start = [NSDate date];
NSArray *jsonArray = (NSArray *)JSON;
NSMutableArray *regionArray = [NSMutableArray new];
for (NSDictionary *jsonDic in jsonArray) {
  RRRegion *region = [[RRRegion alloc] initFromDic:jsonDic];
  [regionArray addObject:region];
}

[RRRegion addToDbRegionArray:regionArray];

NSDate *finish = [NSDate date];
NSTimeInterval interval = [finish timeIntervalSinceDate:start];
NSLog(@"Time consumed to add regions: %f", interval);

  • SQLite on Mac OS X examples

    Here I decided to write some SQLite(SQLite on wiki) on Mac OS X examples of code for creation of database and database management.

    1. Go to the folder where you want to create SQLite database (all this is written on Applications/Services/Terminal):

    cd /Users/user/Documents/

    2. Create database:

    sqlite3 SimpleVocabulary.sql

    3. Create table in that database:

    CREATE TABLE Cards(CardID VARCHAR(5) PRIMARY KEY, Word VARCHAR(1000), Translation VARCHAR(3000), MemoStatus VARCHAR(1));

    4. Insert values to the table:

    INSERT INTO Cards VALUES ("1", "Mother", "Mama", "Y");

    5. Select values from table:

    SELECT * FROM Cards;

    Another code examples:

    1. cd /Users/user/Documents/
    2. sqlite3 TexDatabase.sql
    1.SELECT * FROM Lessons;
    2.SELECT COUNT(*) FROM Lessons;
    3.DELETE FROM Lessons;
    4.CREATE TABLE Lessons(LessonID VARCHAR(5) PRIMARY KEY, LessonGroup VARCHAR(5), LessonTopic VARCHAR(100), LessonText VARCHAR(20000), LessonCode VARCHAR(100), LessonPicture VARCHAR(100));
    5.Drop table Lessons;
    6..schema

    Here .schema command allows you to look at your tables structure.

    Another example:
    1. cd /Users/user/Desktop/Alwawee/AlwaweeApps/Бизнес-цитаты/Database
    2. sqlite3 BusCitDatabase.sql
    3. sqlite> CREATE TABLE BusCits(ID VARCHAR(5) PRIMARY KEY, Text VARCHAR(3000), Author VARCHAR(500));
    4. sqlite> .quit
    5. SELECT COUNT(*) FROM BusCits;
    6. SELECT COUNT(*) FROM BusAuthors;
    7. sqlite> CREATE TABLE BusAuthors(AuthorID VARCHAR(5) PRIMARY KEY, AuthorName VARCHAR(100), AuthorInfo VARCHAR(3000));
    8. SELECT COUNT(*) FROM BusAuthors;
    9. DELETE FROM BusAuthors;
    10. DELETE FROM BusCits;
    11. SELECT DISTINCT Author FROM BusCits WHERE Author NOT IN(SELECT AuthorName FROM BusAuthors);
    12. cd /Users/user/Documents/
    13. cd SimpVoc
    14. sqlite3 TransWords.sql
    15. INSERT INTO Cards VALUES ("1", "Mother", "Madre", "Y");
    16. INSERT INTO Cards VALUES ("2", "Example", "Ejemplo", "Y");
    17. INSERT INTO Cards VALUES ("3", "World", "Mundo", "Y");
    18. SELECT * FROM Cards;

    Here .quit command quits sqlite> mode on your Terminal.