QT Creator + SQLite. Insert Very slow [duplicate]

Question

I made a little terminal app to run in Windows 32bit.

This app listens from UDP port and write data on SQLite database

the Code:

#include <QUdpSocket>
#include <QTextStream>
#include <QSqlDriver>
#include <QSqlDatabase>
#include <QSqlQuery>


int main()
{
    int i;
    QTextStream qout(stdout);

    //db conn or create db
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("localhost");
    db.setDatabaseName("C:\\db_test\\db.sqlite");

    db.open();
    //create db structure
    QSqlQuery *query = new QSqlQuery(db);
    query->prepare("CREATE TABLE "
                   "MYTABLE("
                   "FIELD_1 VARCHAR(100) NOT NULL, "
                   "FIELD_2 VARCHAR(100) NOT NULL)"
                   );

    if(query->exec() == true){
        qout << "New Database Created" << endl;
    } else {
        qout <<"Database Alredy Exists" << endl;
    }


   //start UDP listener
    QUdpSocket *udpSocket = new QUdpSocket(0);
    udpSocket->bind(7755, QUdpSocket::ShareAddress);
    i=1;

    while (udpSocket->waitForReadyRead(-1)) {

        while(udpSocket->hasPendingDatagrams()) {
            QByteArray datagram;
            datagram.resize(udpSocket->pendingDatagramSize());
            QHostAddress sender;
            quint16 senderPort;

            udpSocket->readDatagram(datagram.data(), datagram.size(),
                                    &sender, &senderPort);

            QString Rec_Data = datagram.data();
            QString Sender_Address = sender.toString();

            QString InsertStr = "INSERT INTO MYTABLE VALUES (:val1, :val2)";
            qout << InsertStr << " " << i << endl;
            query->prepare(InsertStr);
            query->bindValue(":val1", Rec_Data);
            query->bindValue(":val2", Sender_Address);
            if(query->exec() == true){
                qout << "Data stored" << endl;
            } else {
                qout <<"Store Error" << endl;
            }

            i=i+1;
        }
    }
}

I need to put about 20,000 queries per minute but when I run (Ctrl+R) it processes about 500 per minute.

I know that something is wrong but I do not know what.

Answer 1

Batch several INSERT operations into a single transaction.

Doing the inserts individually is going to limit your throughput to about 60 inserts per second, due to the hard drive disk platters having to rotate completely around while SQLite does a read-after-write verification.

Further Reading
INSERT is really slow - I can only do few dozen INSERTs per second