Sqlite insertion takes lot of time

205 Views Asked by At

This is one insertion function I am using :

- (void)CreateActivities:(NSMutableArray*)arrActivities{

    for(Activity *activity in arrActivities)
    {
        if(![self ActivityExists:activity.ActivityId])
        {
        sqlite3_threadsafe();
        sqlite3_stmt *statement;

        @try
        {
            @synchronized(self)
            {
                const char *sql = "INSERT INTO Activity (ActivityId, Title, GroupName, Location, StartDate, EndDate, IsRecurrence, IsAdmin, ShowGroup, GroupId, GroupAddedByTeam, MessageCount, IsDeleted, IsGroupAdmin, GroupMessagePermission, Deadline, IsIcalEvent, IsDayActivity)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                NSLog(@"%i", sqlite3_prepare_v2(database, sql, -1, &statement, NULL));
                if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK)
                {
                    sqlite3_bind_text(statement, 1, [[activity ActivityId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 2, [[activity Title] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 3, [[activity GroupName] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 4, [[activity Location] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 5, [[activity StartDate] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 6, [[activity EndDate] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 7, [[activity IsRecurrence] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 8, [[activity IsAdmin] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_int(statement, 9, [[activity ShowGroup] intValue]);
                    sqlite3_bind_text(statement, 10, [[activity GroupId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_int(statement, 11, [[activity GroupAddedByTeam] intValue]);
                    sqlite3_bind_int(statement, 12, [[activity MessageCount] intValue]);
                    sqlite3_bind_int(statement, 13, [[activity IsDeleted] intValue]);
                    sqlite3_bind_int(statement, 14, [[activity IsGroupAdmin] intValue]);
                    sqlite3_bind_int(statement, 15, [[activity GroupMessagePermission] intValue]);
                    sqlite3_bind_text(statement, 16, [[activity Deadline] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_int(statement, 17, [[activity IsIcalEvent] intValue]);
                    sqlite3_bind_int(statement, 18, [[activity IsDayActivity] intValue]);

                    if (sqlite3_step(statement) != SQLITE_DONE) {
                        NSLog(@"Insert failed: %s", sqlite3_errmsg(database));
                    }
                    else
                    {
                        [self CreateInvitations:activity.arrInvitations];
                    }
                }
                else
                {
                    NSLog(@"Prepare-error #%i: %s", sqlite3_prepare_v2(database, sql, -1, &statement, NULL), sqlite3_errmsg(database));
                }
            }
        }
        @catch (NSException *exception) {

            NSLog(@"Error in CreateActivities : %@", exception.description);
        }
        @finally {

            sqlite3_finalize(statement);
        }
        }
    }
}

And this is CreateInvitations :

- (void)CreateInvitations:(NSMutableArray*)arrInviations
{
    for(Invitation *invitation in arrInviations)
    {
        sqlite3_threadsafe();
        sqlite3_stmt *statement;

        @try
        {
            @synchronized(self)
            {
                const char *sql = "INSERT INTO Invitation (ChildName, Response, InvitationId, IsCoGaurdian, ActivityId, Picture, InvitedUserId, RoleId, Status, ParentEmail)VALUES(?,?,?,?,?,?,?,?,?, ?)";

                NSLog(@"%i", sqlite3_prepare_v2(database, sql, -1, &statement, NULL));
                if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK)
                {
                    sqlite3_bind_text(statement, 1, [[invitation ChildName] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_int(statement, 2, [[invitation Response] intValue]);
                    sqlite3_bind_text(statement, 3, [[invitation InvitationId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_int(statement, 4, [[invitation IsCoGaurdian] intValue]);
                    sqlite3_bind_text(statement, 5, [[invitation ActivityId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 6, [[invitation Picture] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 7, [[invitation InvitedUserId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 8, [[invitation RoleId] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 9, [[invitation Status] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 10, [[invitation ParentEmail] UTF8String], -1, SQLITE_TRANSIENT);

                    if (sqlite3_step(statement) != SQLITE_DONE) {
                        NSLog(@"Insert failed: %s", sqlite3_errmsg(database));
                    }
                }
                else
                {
                    NSLog(@"Prepare-error #%i: %s", sqlite3_prepare_v2(database, sql, -1, &statement, NULL), sqlite3_errmsg(database));
                }
            }
        }
        @catch (NSException *exception) {

            NSLog(@"Error in CreateInvitations : %@", exception.description);
        }
        @finally {

            sqlite3_finalize(statement);
        }
    }
}

The whole operation takes around 4 seconds for inserting 190 records (95 Activity and 95 Invitation), which I think is quite much.
How should I make it better ?

0

There are 0 best solutions below