我用Qt编写了一个C++可执行程序，用于计算基于SQLite数据库的数据并将其再次存储在数据库中。计算数据并通过UPDATE再次存储它。最初我用大约5000行的块测试了它，它工作得相当快（每行大约1 ms）。现在我实现了对大约600000行的数据库应用相同计算所需的一切。现在每行的平均时间大约慢了200倍。在改进我的代码时，我首先验证了：这与表的大小有关，而与计算细节无关，因此我将长表中的数据截断为100000行，这只比具有5000行的小表慢20倍。
我从一开始就在代码中使用编译指示来提高整体性能：

query.exec("PRAGMA page_size = 16384");
    query.exec("PRAGMA cache_size = 131072");
    query.exec("PRAGMA temp_store = MEMORY");
    query.exec("PRAGMA journal_mode = OFF");
    query.exec("PRAGMA locking_mode = EXCLUSIVE");
    query.exec("PRAGMA synchronous = OFF");

我还尝试使用100行或1000行的事务，但这没有帮助：

database.transaction();
...
database.commit();

有人能建议怎么做吗？我已经在考虑分割数据库文件了，但是有没有简单的方法可以做到呢？

**EDIT：**按照要求，这里是一个简化的最小示例。在我的真实的示例中，表格布局更加复杂（17列），大约有600000个条目，但这也很好地引入了问题：

widget.h

#ifndef WIDGET_H
#define WIDGET_H

#include <QtCore>
#include <QtGui>
#include <QtWidgets>
#include <QtSql>

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = 0)
        : QWidget(parent)
    {
        QPushButton *createSmall = new QPushButton("Create Small");
        connect(createSmall, SIGNAL(clicked()), this, SLOT(createSmallDataBase()));
        QPushButton *createBig = new QPushButton("Create Big");
        connect(createBig, SIGNAL(clicked()), this, SLOT(createBigDataBase()));
        QPushButton *calculateSmall = new QPushButton("Calculate Small");
        connect(calculateSmall, SIGNAL(clicked()), this, SLOT(calculateSmallDataBase()));
        QPushButton *calculateBig = new QPushButton("Calculate Big");
        connect(calculateBig, SIGNAL(clicked()), this, SLOT(calculateBigDataBase()));
        QVBoxLayout *layout = new QVBoxLayout();
        layout->addWidget(createSmall);
        layout->addWidget(createBig);
        layout->addWidget(calculateSmall);
        layout->addWidget(calculateBig);
        this->setLayout(layout);
    }

    ~Widget()
    {
    }

    void createDataBase(quint32 size, QString name)
    {
        QSqlDatabase database;
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName(name);
        if(database.open())
        {
            QSqlQuery query(database);
            query.exec("PRAGMA page_size = 4096");
            query.exec("PRAGMA cache_size = 16384");
            query.exec("PRAGMA temp_store = MEMORY");
            query.exec("PRAGMA journal_mode = OFF");
            query.exec("PRAGMA locking_mode = EXCLUSIVE");
            query.exec("PRAGMA synchronous = OFF");
            qDebug() << "DROP" << query.exec("DROP TABLE Scenario");
            qDebug() << "CREATE" << query.exec("CREATE TABLE IF NOT EXISTS Scenario(id INTEGER, time REAL, prob REAL)");
            for(quint32 i = 0; i < size; i++)
            {
                query.exec(QString("INSERT INTO Scenario (id, time, prob) VALUES(%1, %2, %3)").arg(i).arg(i).arg(-1));
            }
        }
        database.close();
        database.removeDatabase("QSQLITE");
    }

    void calculateDataBase(QString name)
    {
        QSqlDatabase database;
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName(name);
        if(database.open())
        {
            QSqlQuery query(database);
            query.exec("PRAGMA page_size = 4096");
            query.exec("PRAGMA cache_size = 16384");
            query.exec("PRAGMA temp_store = MEMORY");
            query.exec("PRAGMA journal_mode = OFF");
            query.exec("PRAGMA locking_mode = EXCLUSIVE");
            query.exec("PRAGMA synchronous = OFF");

            query.exec("SELECT MAX(id) FROM Scenario");
            quint32 maxID = 0;

            if(query.next())
            {
                maxID = query.value(0).toUInt();
            }

            for(quint32 id = 0; id <= maxID; id++)
            {
                query.exec(QString("SELECT id, time, prob FROM Scenario WHERE id = %1").arg(QString::number(id)));
                if(query.first())
                {
                    double prob = query.value(0).toDouble();
                    query.exec(QString("UPDATE Scenario SET prob = %1 WHERE id = %2").arg(qSqrt(prob)).arg(QString::number(id)));
                }
            }
        }
        database.close();
        database.removeDatabase("QSQLITE");
    }

public slots:
    void createSmallDataBase()
    {
        QTime time;
        time.start();
        createDataBase(1000, "small.sqlite");
        qDebug() << "Create Small" << time.elapsed()/1000.0;
    }
    void createBigDataBase()
    {
        QTime time;
        time.start();
        createDataBase(10000, "big.sqlite");
        qDebug() << "Create Big" << time.elapsed()/10000.0;
    }
    void calculateSmallDataBase()
    {
        QTime time;
        time.start();
        calculateDataBase("small.sqlite");
        qDebug() << "Calculate Small" << time.elapsed()/1000.0;
    }
    void calculateBigDataBase()
    {
        QTime time;
        time.start();
        calculateDataBase("big.sqlite");
        qDebug() << "Calculate Big" << time.elapsed()/10000.0;
    }
};

#endif // WIDGET_H

main.cpp

#include <QApplication>

#include "widget.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    Widget w;
    w.show();

    return a.exec();
}

在我的机器上，调用calculateSmallDataBase()和calculateBigDataBase()之间的差异是从0.518ms/行到3.0417ms/行，而这只是从1000行到10000行！所以我已经达到了这两者之间的因子6。
期待您的建议。