Qt之Sqlite insert 方法对比

在台式机上测试Sqlite不同插入方案对比发现：select-stmt是插入速度最快的方式，事务虽然也能加快插入数据，相较于STMT还是慢了不少。

#include <QCoreApplication>

#include <QSqlQuery>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlError>
#include <QDateTime>
#include <QDebug>

class SqlInsert
{
public:
    SqlInsert()
    {
        db = QSqlDatabase::addDatabase("QSQLITE","InsertAction");
        db.setDatabaseName("InsertOP.db");
        db.open();
        QSqlQuery query(db);
        query.exec("create table TestTable (id INTEGER PRIMARY KEY"
                   ", Datetime varchar(30), System varchar(10),"
                   "Type varchar(10), MainItem varchar(20),"
                   "SubItem varchar(20), Result varchar(20))");
    }
    virtual ~SqlInsert()
    {
        db.close();
        db.removeDatabase("SqlThread");
    }
    // 直接插入
    void insertbynormalway()
    {
        QSqlQuery query(QSqlDatabase::database("InsertAction"));
        for(int i = 0; i < 100; i++)
        {
            query.exec("insert into TestTable(Datetime, System, Type, MainItem, SubItem, Result)values('2021-05-09 18:19:12','A','1','2','3','4')");
        }
    }
    // STMT插入
    void insertbystmtway()
    {
         QSqlQuery query(QSqlDatabase::database("InsertAction"));
         QString strDateTime = QDate::currentDate().toString("yyyy-MM-dd ") + QTime::currentTime().toString("hh:mm:ss.zzz");
         QString strSystem = "A";
         QString strEb = "INSERT INTO TestTable SELECT " \
                        + QString::number(0) + "AS 'id','" \
                        + strDateTime + "'AS 'Datetime','" \
                        + strSystem +"'AS 'System','" \
                        + QString::number(1) + "'AS 'Type', '" \
                        + QString::number(2) + "'AS 'MainItem', '"  \
                        + QString::number(3) + "'AS 'SubItem', '" \
                        + QString::number(4) + "'AS 'Result'";
         QString strunionEb = "";
         for (int i = 1; i < 100; i++)
         {
             strunionEb = "UNION ALL SELECT " \
                          + QString::number(i) + ",'" \
                          + strDateTime + "','" \
                          + strSystem + "','" \
                          + QString::number(1) + "','" \
                          + QString::number(2) + "','" \
                          + QString::number(3) + "','" \
                          + QString::number(4) + "' ";
             strEb = strEb + strunionEb;
         }
         query.exec(strEb);
    }
    // 事务插入
    void insertbytransaction()
    {
        QSqlQuery query(QSqlDatabase::database("InsertAction"));
        if(db.driver()->hasFeature(QSqlDriver::Transactions))
        {//支持事务操作
            if(db.transaction())/// 开启事务
            {
                bool retval = true;
                query.setForwardOnly(true);
                query.prepare("insert into TestTable(Datetime, System, Type, MainItem, SubItem, Result)values(:datetime,:system,:type,:mainItem,:subItem,:result)");
                for(int i = 0; i < 100;i++ )
                {
                    QString strDateTime = QDate::currentDate().toString("yyyy-MM-dd ") + QTime::currentTime().toString("hh:mm:ss.zzz");
                    QString strSystem = "A";
                    query.bindValue(":datetime",strDateTime);
                    query.bindValue(":system",strSystem);
                    query.bindValue(":type",QString::number(1));
                    query.bindValue(":mainItem",QString::number(2));
                    query.bindValue(":subItem",QString::number(3));
                    query.bindValue(":result",QString::number(4));
                    retval &= query.exec();
                }
                if(retval)
                {
                    if(!db.commit())
                    {
                        qDebug() << QString("%1[%2 LineNo:%3]Database Commit Failed,Cause:%4")\
                                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz"))\
                                    .arg(__FUNCTION__).arg(__LINE__).arg(db.lastError().databaseText());
                    }
                }
                else
                {
                    if(!db.rollback())//如果提交失败则回滚
                    {
                        qDebug() << QString("%1[%2 LineNo:%3]Database Rollback Failed,Cause:%4")\
                                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz"))\
                                    .arg(__FUNCTION__).arg(__LINE__).arg(db.lastError().databaseText());
                    }
                }
            }
            else
            {
                qDebug() << QString("%1[%2 LineNo:%3]Database Transaction Failed,Cause:%4")\
                            .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz"))\
                            .arg(__FUNCTION__).arg(__LINE__).arg(db.lastError().databaseText());
            }
        }
        else
        {
            qDebug() << QString("Database cannot support transaction function");
        }
    }
    void CleanTableContent()
    {
        QSqlQuery query(QSqlDatabase::database("InsertAction"));
        query.exec("DELETE FROM TestTable");
    }
private:
     QSqlDatabase db;
};

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    SqlInsert work;
    {
        QDateTime CurrentTime1 = QDateTime::currentDateTime();
        work.insertbynormalway();
        QDateTime CurrentTime2 = QDateTime::currentDateTime();
        int32_t ms = CurrentTime1.msecsTo(CurrentTime2);
        qDebug() << QString("normal way insert action takes time:%1").arg(ms);
        work.CleanTableContent();
    }
    {
        QDateTime CurrentTime1 = QDateTime::currentDateTime();
        work.insertbystmtway();
        QDateTime CurrentTime2 = QDateTime::currentDateTime();
        int32_t ms = CurrentTime1.msecsTo(CurrentTime2);
        qDebug() << QString("stmt way insert action takes time:%1").arg(ms);
        work.CleanTableContent();
    }
    {
        QDateTime CurrentTime1 = QDateTime::currentDateTime();
        work.insertbytransaction();
        QDateTime CurrentTime2 = QDateTime::currentDateTime();
        int32_t ms = CurrentTime1.msecsTo(CurrentTime2);
        qDebug() << QString("transaction way insert action takes time:%1").arg(ms);
    }
    return a.exec();
}