如何快速将40000条记录插入iPad中的sqlite数据库 [英] How to insert 40000 records fast into an sqlite database in an iPad

问题描述

我想在我的iPad应用程序中将从Web服务获取的40000条记录插入到sqlite数据库中。

I want to insert 40000 records that i get from a web service into a sqlite database in my iPad app.

我编写了以下代码，但它需要20分钟，有更快的方式吗？

I wrote the following code, but it takes around 20 minutes, is there a faster way?

- (NSArray *)insertPriceSQLWithPrice:(Price *) price
{

SQLiteManager *dbInfo = [SQLiteManager sharedSQLiteManagerWithDataBaseName:@"codefuel_catalogo.sqlite"];


sqlite3 *database;

NSString *querySQL=[self formatStringQueryInsertWithTable:@"prices_list" andObject:price];


if(sqlite3_open([dbInfo.dataBasePath UTF8String], &database) == SQLITE_OK)
{
    sqlite3_stmt * compiledStatement;


    const char *query_stmt = [querySQL UTF8String];

    int result = sqlite3_prepare_v2(database, query_stmt, -1, &compiledStatement, NULL);

    if (result == SQLITE_OK)
    {
        int success = sqlite3_step(compiledStatement);

        NSLog(@"el numero de success es -> %i",success);
        if (success == SQLITE_ERROR)
            NSLog(@"Error al insertar en la base de datps");

    }
    else
        NSLog(@"Error %@ ERROR!!!!",querySQL);

    sqlite3_finalize(compiledStatement);
}

sqlite3_close(database);
return nil;
}

推荐答案

你有三件事需要做的是为了加快插入速度：

There are three things that you need to do in order to speed up the insertions:

• 移动 sqlite3_open 在循环之外。目前，循环未显示，因此我认为它不在您的代码段之内


• 添加 BEGIN TRANSACTION 和 COMMIT TRANSACTION 调用 - 您需要在插入循环之前开始事务并在循环结束后立即结束。


• 制作 formatStringQueryInsertWithTable 真正参数化 - 目前看来你没有充分利用预备陈述，因为尽管使用了 sqlite3_prepare_v2 ，但您的代码中没有调用 sqlite3_bind_XYZ 。

• Move the call of sqlite3_open outside the loop. Currently, the loop is not shown, so I assume it is outside your code snippet
• Add BEGIN TRANSACTION and COMMIT TRANSACTION calls - you need to begin transaction before the insertion loop and end it right after the loop is over.
• Make formatStringQueryInsertWithTable truly parameterized - Currently it appears that you are not using prepared statements to their fullest, because despite using sqlite3_prepare_v2, you have no calls of sqlite3_bind_XYZ in your code.

这是一个不错的帖子它告诉你如何做到以上所有。它是普通的C，但它可以作为Objective C程序的一部分正常工作。

Here is a nice post that shows you how to do all of the above. It is plain C, but it will work fine as part of an Objective C program.

char* errorMessage;
sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);
char buffer[] = "INSERT INTO example VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";
sqlite3_stmt* stmt;
sqlite3_prepare_v2(mDb, buffer, strlen(buffer), &stmt, NULL);
for (unsigned i = 0; i < mVal; i++) {
    std::string id = getID();
    sqlite3_bind_text(stmt, 1, id.c_str(), id.size(), SQLITE_STATIC);
    sqlite3_bind_double(stmt, 2, getDouble());
    sqlite3_bind_double(stmt, 3, getDouble());
    sqlite3_bind_double(stmt, 4, getDouble());
    sqlite3_bind_int(stmt, 5, getInt());
    sqlite3_bind_int(stmt, 6, getInt());
    sqlite3_bind_int(stmt, 7, getInt());
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        printf("Commit Failed!\n");
    }
    sqlite3_reset(stmt);
}
sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);
sqlite3_finalize(stmt);

这篇关于如何快速将40000条记录插入iPad中的sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！