скорость наполнения базы

[Aso Renji]

Есть таблица с двумя колонками типа INTEGER. Первая колонка - первичный индекс. В таблицу требуется внести миллион новых строк. Вопрос: как внести этот миллион за время сравнимое со временем запихивания миллиона записей в qMap и последующей выгрузкой qMap на диск? Тип используемой БД - любой, лишь бы базовый синтаксис SQL был. Прочие блага цивилизации вроде триггеров не интересуют.

Взял SQLite, сделал prepare для запроса, все равно через SQL работа идет раз в десять медленнее чем через qMap. Такая разница наводит на мысли о том, что я где-то накосячил. И собственно, тестовый код:

Код:

void test()
{
    const int rowNumber=1000000;

    //SQL test
    QSqlDatabase myBase=QSqlDatabase::addDatabase("QSQLITE");
    myBase.setDatabaseName("testBase");
    myBase.open();
    QSqlQuery query;
    query.exec("DROP TABLE testTable");
    query.exec("CREATE TABLE testTable(field1 INTEGER PRIMARY KEY,field2 INTEGER)");
    myBase.transaction();
    QSqlQuery insert;
    insert.prepare("INSERT INTO testTable (field1,field2) VALUES(?,?)");
    QTime time;
    time.start();
    for(int i=0;i<rowNumber;++i)
    {
        insert.addBindValue(QVariant(i));
        insert.addBindValue(QVariant(i));
        insert.exec();
    }
    myBase.commit();
    qDebug()<<"SQL time = "<<time.elapsed();
    myBase.close();

    //qMap test
    time.start();
    QFile F("testFile");
    F.open(QIODevice::WriteOnly);
    QDataStream stream(&F);
    QMap<int,int>testMap;
    for(int i=0;i<rowNumber;++i)
        testMap[i]=i;
    stream<<testMap;
    F.close();
    qDebug()<<"map time = "<<time.elapsed();
}

[fuCtor]

Попробуй делать комимт порциями, загрузил например 1000 элементов, сохранил и так далее. Потенциально должно ускорить относительно одного общего.

[alexis031182]

Транзакции и множественные VALUES

[DmitryM]

Такая разница наводит на мысли о том, что я где-то накосячил.

Конечно накосячил, а именно в создание таблицы

Код

SQL
CREATE TABLE testTable(field1 INTEGER PRIMARY KEY,field2 INTEGER)
 

PRIMARY KEY накладывает следующие ограничения: UNIQUE NOT NULL.
Соответственно любая приличная СУБД проверяет то, что ты туда пихаешь.
Вообще имеет смысл рассмотреть key-value storage, но это уже NoSQL

[xokc]

1. Создавать индекс после вставки записей
2. Для вставки использовать BULK INSERT

[DmitryM]

Автор сравнивает мягкое с теплым и удивляется
С чего prepare, addBindValue, exec будет отрабатывать за сравнимое время c оператором [] QMap'a?

[xokc]

Ну немного не так. Ему же надо наполнить QMap и записать его на диск. В некоторых случаях это вполне соизмеримо с BULK INSERT и последующем созданием первичного ключа.

[Aso Renji]

Автор сравнивает мягкое с теплым и удивляется
С чего prepare, addBindValue, exec будет отрабатывать за сравнимое время c оператором [] QMap'a?

Время вставки в B-tree (в СУБД ведь его используют?), не должно существенно отличаться от времени вставки в balanced tree используемом во всяких там (q)map. По крайней мере, пока все нужные данные болтаются в кеше, а он у SQLite большой (подкурить руками пробовал, не помогает). Время выгрузки данных на диск тоже не должно существенно различаться. Объемы то те же самые. Вот если движок SQL при каждом INSERT гоняет парсер, тады ой. Но я непойму, зачем тогда prepare придуман.

И да, убирать PRIMARY KEY пробовал. Через каждые тысячу вставок начинать новую транзакцию пробовал. Все равно тормозит. Видимо, придется смотреть в сторону NoSQL решений.

[Странник]

PRAGMA synchronous;
PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL;

Query or change the setting of the "synchronous" flag. The first (query) form will return the synchronous setting as an integer. When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.

In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions. With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss, but they do not aid in preserving consistency. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode.

The default setting is synchronous=FULL.

See also the fullfsync and checkpoint_fullfsync pragmas.

[Aso Renji]

PRAGMA synchronous;

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

[Alex_C]

Да ребят)) Ну вы блин даете)))
Такую фигню пишите - без обид.
Делал программу, в которой было очень принципиально время занесения в БД.. Долго в этом разбирался.
Теперь по сути: Вы хотите занести данные в ... куда угодно, но не в БД - конечно это быстрее, потому как при внесении данных в БД создаются ключи, а если БД еще и OLAP - еще и связи - медленно. Есть варианты как занести быстро - но они платформозависимы. Ну как то так)))

[Aso Renji]

Теперь по сути: Вы хотите занести данные в ... куда угодно, но не в БД - конечно это быстрее, потому как при внесении данных в БД создаются ключи

А при использовании qMap они, конечно, не создаются, потому что разработчик - идиот. Я вас очень удивлю, но и std::map, и qMap, за вычетом некоторых тонкостей работают по тем же принципам что таблица БД с кластерным индексом. Разница между B-tree и balanced tree не принципиальна.

[twp]

Время вставки в B-tree (в СУБД ведь его используют?), не должно существенно отличаться от времени вставки в balanced tree используемом во всяких там (q)map. По крайней мере, пока все нужные данные болтаются в кеше, а он у SQLite большой (подкурить руками пробовал, не помогает). Время выгрузки данных на диск тоже не должно существенно различаться. Объемы то те же самые. Вот если движок SQL при каждом INSERT гоняет парсер, тады ой. Но я непойму, зачем тогда prepare придуман.

И да, убирать PRIMARY KEY пробовал. Через каждые тысячу вставок начинать новую транзакцию пробовал. Все равно тормозит. Видимо, придется смотреть в сторону NoSQL решений.

QMap не является деревом.

The QMap class is a template class that provides a skip-list-based dictionary.

prepare скорее всего не поддерживается в SQLite, его поддерживают СУБД типа Oracle. Это можно проверить через

Код

C++ (Qt)
QSqlDriver::hasFeature(QSqlDriver::PreparedQueries)
 

Аналогично можно проверить поддержку пакетных операций QSqlQuery::execBatch() и транзакций

Код

C++ (Qt)
QSqlDriver::BatchOperations
QSqlDriver::Transactions
 

Не думаю что SQLite их поддерживает
В итоге скорее всего самый быстрый способ для SQLite (как уже было предложено), это создать таблицу с одним полем, и после вставки всех данных добавить уникальный ключ.

[Aso Renji]

QMap не является деревом.

Действительно, ступил. Впрочем, даже после замены qMap на точно древовидный std::map map все равно работает раз в пять быстрее БД.

prepare скорее всего не поддерживается в SQLite, его поддерживают СУБД типа Oracle.

По документации поддерживается и prepare (http://www.sqlite.org/c3ref/prepare.html), и транзакции (http://sqlite.org/lang_transaction.html). И по hasFeature все есть. А вот BatchOperations действительно, нет. Правда, при попытке прикрутить MySQL (тоже тормозит), Qt говорит что этого BatchOperations и там нет. Он вообще где либо есть?

[twp]

я чего-то думал что SQLite менее продвинута. Ну по идее оракл и мелкософтовская субд поддерживает batch опреации. Ну еще как вариант можно попробовать использовать кастом драйвер к базе данных. Хотя я не думаю что можно добиться совместимых по скорости результатов с QMap. Все таки СУБД поддерживает целостность данных и проверяет типы данных которые добавляются в базу.