Как быстрее всего вставить 100 000 записей из одной базы данных в другую?

Я не думаю, что присоединение двух баз данных и запуск INSERT INTO foo (SELECT * FROM bar) - самый быстрый способ сделать это. Если вы выполняете синхронизацию между портативным устройством и сервером (или другим устройством), может ли транспортный механизм быть узким местом? Или два файла базы данных уже находятся в одной файловой системе? Если файловая система на устройстве медленнее flash-памяти, может ли это быть узким местом?

Можете ли вы скомпилировать / запустить необработанный код SQLite C на своем устройстве? (Я думаю, что объединение RAW sqlite3 должно компилироваться для WinCE / Mobile) Если это так, и вы готовы:

• Чтобы написать код C (используя SQLite C API)
• Увеличьте риск потери данных, отключив ведение журнала диска

Должна существовать возможность написать небольшой автономный исполняемый файл для очень быстрого копирования / синхронизации 100К записей между двумя базами данных.

Я разместил здесь кое-что из того, что узнал об оптимизации вставок SQLite: Улучшить ВСТАВИТЬ производительность SQLite в секунду?

Изменить: Пробовал это с реальным кодом ...

Я не знаю всех этапов создания исполняемого файла 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 <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#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 этот код копирует 100 тыс. записей из source.sqlite в dest.sqlite за 1,20 секунды. Я не знаю точно, какую производительность вы увидите на мобильном устройстве с флэш-памятью (но я любопытный).

Если целью является некоторая версия 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, если включите ее в свой пакет.

Я сейчас мобильный, поэтому не могу опубликовать подробный ответ, но, возможно, это стоит прочитать:

http://sqlite.org/cvstrac/wiki?p=SpeedComparison

Как видите, SQLite 3 выполняет операции INSERT намного быстрее при использовании индексов и / или транзакций. Кроме того, INSERTs FROM SELECT, похоже, не является сильной стороной SQLite.

INSERT INTO SELECT * из подключенных баз данных - самый быстрый доступный вариант в SQLite. Несколько вещей, на которые стоит обратить внимание.

1. Сделки. Убедитесь, что все находится внутри транзакции. Это действительно критично. Если это только один оператор SQL, это не важно, но вы сказали, что журнал увеличивается «шаг за шагом», что указывает на то, что это более одного оператора.

2. Триггеры. У вас есть триггеры? Очевидно, это могло повлиять на производительность.

3. Ограничения. У вас есть ненужные ограничения? Вы не можете отключить их или удалить / повторно добавить их, поэтому, если они необходимы, вы мало что можете с ними поделать, но это то, что нужно учитывать.

Вы уже упоминали об отключении индексов.

Все 100 000 записей меняются очень часто? Или это подмножество, которое меняется?

Если это так, вам следует подумать о добавлении столбца updated_since_last_sync, который помечается при выполнении обновления, поэтому во время следующей синхронизации вы копируете только те записи, которые действительно изменились. После копирования записей вы снова устанавливаете столбец флага на ноль.

Отправляйте только дельты. Т.е. Присылайте только различия. Т.е. Отправляйте только то, что изменилось.

Как насчет хранения таблицы базы данных sync.table в отдельном файле? Таким образом, вам просто нужно сделать копию этого файла для синхронизации. Бьюсь об заклад, это намного быстрее, чем синхронизация с помощью SQL.

Если вы еще этого не сделали, вам нужно обернуть это транзакцией. Имеет значительную разницу в скорости.