从一个数据库向另一个数据库插入100 000条记录的最快方法是什么？

我有一个移动应用程序.我的客户端有大量数据集~100,000条记录.它经常更新.我们同步时需要从一个数据库复制到另一个数据库.

我已将第二个数据库附加到main,并运行一个insert into table select * from sync.table.

这非常慢,我认为大约需要10分钟.我注意到日志文件逐步增加.

我怎样才能加快速度呢？

编辑1

我有索引,我有日记.运用

insert into table select * from sync.table

它还需要10分钟.

编辑2

如果我运行像这样的查询

select id,invitem,invid,cost from inventory where itemtype = 1 
order by invitem limit 50 

需要15-20秒.

表模式是:

CREATE TABLE inventory  
('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 'serverid' INTEGER NOT NULL DEFAULT 0,
 'itemtype' INTEGER NOT NULL DEFAULT 0,
 'invitem' VARCHAR,
 'instock' FLOAT  NOT NULL DEFAULT 0,
 'cost' FLOAT NOT NULL DEFAULT 0,
 'invid' VARCHAR,
 'categoryid' INTEGER  DEFAULT 0,
 'pdacategoryid' INTEGER DEFAULT 0,
 'notes' VARCHAR,
 'threshold' INTEGER  NOT NULL DEFAULT 0,
 'ordered' INTEGER  NOT NULL DEFAULT 0,
 'supplier' VARCHAR,
 'markup' FLOAT NOT NULL DEFAULT 0,
 'taxfree' INTEGER NOT NULL DEFAULT 0,
 'dirty' INTEGER NOT NULL DEFAULT 1,
 'username' VARCHAR,
 'version' INTEGER NOT NULL DEFAULT 15
)

索引创建如下

CREATE INDEX idx_inventory_categoryid ON inventory (pdacategoryid);
CREATE INDEX idx_inventory_invitem ON inventory (invitem);
CREATE INDEX idx_inventory_itemtype ON inventory (itemtype);

我想知道,插入... select*from不是最快速的内置方式来进行海量数据复制吗？

编辑3

SQLite是无服务器的,所以请停止投票给一个特定的答案,因为这不是我肯定的答案.

1> jspcal..：

---

如果目标是某个版本的MS SQL Server,则SqlBulkCopy为大型数据集提供了有效的插入,这与命令类似bcp.

您还可以在插入之前禁用/删除非聚集索引,然后重新创建它们.

在SQLite中,这些通常非常快:

.dump ?TABLE? ...      Dump the database in an SQL text format
.import FILE TABLE     Import data from FILE into TABLE

还试试: PRAGMA journal_mode = OFF

仅供参考,如果将其包含在您的软件包中,您应该可以在Windows Mobile上运行命令行实用程序.

---

对于sqlite尝试`.dump/.import`命令而不是select into(你的标签没有指定两个dbs都是sqlite)

2> Mike Willeke..：

---

我不认为附加两个数据库并运行INSERT INTO foo (SELECT * FROM bar)是最快的方法.如果您在手持设备和服务器(或其他设备)之间进行同步,那么传输机制是否会成为瓶颈？或者两个数据库文件是否已存在于同一个filesysem中？如果设备上的文件系统闪存速度较慢,这可能是瓶颈吗？

您是否能够在设备上编译/运行原始SQLite C代码？(我认为RAW sqlite3合并应该为WinCE/Mobile编译)如果是这样,你愿意:

编写一些C代码(使用SQLite C API)

通过关闭磁盘日记功能来增加数据丢失的风险

应该可以编写一个小的独立可执行文件,以便非常快速地复制/同步两个数据库之间的100K记录.

我在这里发布了一些关于优化SQLite插入的知识:提高 SQLite 的每秒INSERT性能？

---

编辑: 尝试使用真实代码...

我不知道构建Windows Mobile可执行文件所涉及的所有步骤,但SQLite3合并应该使用Visual Studio进行现成的编译.下面是一个示例main.c程序,它打开两个SQLite数据库(两者都必须具有相同的模式 - 请参阅#define TABLE语句)并执行SELECT语句,然后将结果行绑定到INSERT语句:

/*************************************************************
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**************************************************************/
#include 
#include 
#include 
#include 
#include "sqlite3.h"

#define SOURCEDB "C:\\source.sqlite"
#define DESTDB "c:\\dest.sqlite"

#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * sourceDB;
    sqlite3 * destDB;

    sqlite3_stmt * insertStmt;
    sqlite3_stmt * selectStmt;

    char * insertTail = 0;
    char * selectTail = 0;

    int n = 0;
    int result = 0;
    char * sErrMsg = 0;
    clock_t cStartClock;

    char sInsertSQL [BUFFER_SIZE] = "\0";
    char sSelectSQL [BUFFER_SIZE] = "\0";

    /* Open the Source and Destination databases */
    sqlite3_open(SOURCEDB, &sourceDB);
    sqlite3_open(DESTDB, &destDB);

    /* Risky - but improves performance */
    sqlite3_exec(destDB, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
    sqlite3_exec(destDB, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

    cStartClock = clock(); /* Keep track of how long this took*/

    /* Prepared statements are much faster */
    /* Compile the Insert statement */
    sprintf(sInsertSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
    sqlite3_prepare_v2(destDB, sInsertSQL, BUFFER_SIZE, &insertStmt, &insertTail);

    /* Compile the Select statement */
    sprintf(sSelectSQL, "SELECT * FROM TTC LIMIT 100000");
    sqlite3_prepare_v2(sourceDB, sSelectSQL, BUFFER_SIZE, &selectStmt, &selectTail);

    /* Transaction on the destination database */
    sqlite3_exec(destDB, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

    /* Execute the Select Statement.  Step through the returned rows and bind
    each value to the prepared insert statement.  Obviously this is much simpler
    if the columns in the select statement are in the same order as the columns
    in the insert statement */
    result = sqlite3_step(selectStmt);
    while (result == SQLITE_ROW)
    {

        sqlite3_bind_text(insertStmt, 1, sqlite3_column_text(selectStmt, 1), -1, SQLITE_TRANSIENT); /* Get Route */
        sqlite3_bind_text(insertStmt, 2, sqlite3_column_text(selectStmt, 2), -1, SQLITE_TRANSIENT); /* Get Branch */
        sqlite3_bind_text(insertStmt, 3, sqlite3_column_text(selectStmt, 3), -1, SQLITE_TRANSIENT); /* Get Version */
        sqlite3_bind_text(insertStmt, 4, sqlite3_column_text(selectStmt, 4), -1, SQLITE_TRANSIENT); /* Get Stop Number */
        sqlite3_bind_text(insertStmt, 5, sqlite3_column_text(selectStmt, 5), -1, SQLITE_TRANSIENT); /* Get Vehicle */
        sqlite3_bind_text(insertStmt, 6, sqlite3_column_text(selectStmt, 6), -1, SQLITE_TRANSIENT); /* Get Date */
        sqlite3_bind_text(insertStmt, 7, sqlite3_column_text(selectStmt, 7), -1, SQLITE_TRANSIENT); /* Get Time */

        sqlite3_step(insertStmt);       /* Execute the SQL Insert Statement (Destination Database)*/
        sqlite3_clear_bindings(insertStmt); /* Clear bindings */
        sqlite3_reset(insertStmt);      /* Reset VDBE */

        n++;

        /* Fetch next from from source database */
        result = sqlite3_step(selectStmt);

    }

    sqlite3_exec(destDB, "END TRANSACTION", NULL, NULL, &sErrMsg);

    printf("Transfered %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_finalize(selectStmt);
    sqlite3_finalize(insertStmt);

    /* Close both databases */
    sqlite3_close(destDB);
    sqlite3_close(sourceDB);

    return 0;
}

在我的Windows桌面计算机的代码拷贝100K记录从source.sqlite到dest.sqlite1.20秒. 我不清楚你会在具有闪存的移动设备上看到什么样的性能(但我很好奇).