Подтвердить что ты не робот

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

У меня есть мобильное приложение. Мой клиент имеет большой набор данных ~ 100 000 записей. Он часто обновляется. При синхронизации мы должны копировать из одной базы данных в другую.

Я привязал вторую базу данных к главному и запустил insert into table select * from sync.table.

Это очень медленно, мне кажется, это занимает около 10 минут. Я заметил, что файл журнала увеличивается шаг за шагом.

Как я могу ускорить это?

EDITED 1

У меня индексы отключены, и у меня есть журнал. Использование

insert into table select * from sync.table

он по-прежнему занимает 10 минут.

EDITED 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 не самый быстрый встроенный способ сделать массивную копию данных?

EDITED 3

SQLite не имеет сервера, поэтому, пожалуйста, остановите голосование определенным ответом, потому что это не тот ответ, который я уверен.

4b9b3361

ОТВЕТЫ

Ответ 1

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

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

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

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

Я опубликовал некоторые из того, что я узнал об оптимизации вложений SQLite здесь: Улучшить производительность SQLite в SQL Server?

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

Я не знаю всех шагов, связанных с созданием исполняемого файла 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 секунды. Я точно не знаю, какую производительность вы увидите на мобильном устройстве с флеш-памятью (но мне любопытно).

Ответ 2

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

FYI, вы должны иметь возможность запускать утилиту командной строки на Windows Mobile, если вы включили ее в свой пакет.

Ответ 3

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

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

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

Ответ 4

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

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

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

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

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

Ответ 5

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

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

Ответ 6

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

Ответ 7

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

Ответ 8

Если вам еще не нужно обернуть его в транзакцию. Значительная разница в скорости.