Qt program hangs during insertion of 1023 rows of data into mysql database

Question

I am writing a voucher management system for vouchers generated by pfsense. The program is relatively simply as it will need to import the csv files generated by pfsense, insert them into a database and then be able to print them as receipts. I have written the function to insert the data into the database however when I run it the program hangs for a bit and displays not responding. Is there any way to make the program wait, as in pause, for the execution to finish without saying it has stopped responding? Any help would be appreciated. My function is as follows:

void MainWindow::updateVouchers()
{
int insertions = 0;//used to keep track of total number of successful insertions into database
int total = 0; //used to keep track of how many lines of codes need to be inserted

//check if a the user selected a valid csv file and gave a valid voucher expiration time
if (im_FileName.isNull() || im_FileName.isEmpty() || voucherTime <= 0 )
{
    ui->statusBar->showMessage("Please select a voucher file to import and enter a valid time",5000);
    return;
}

{
    QSqlDatabase dbcon = QSqlDatabase::addDatabase("QMYSQL","updateconnection");

    QString host = "localhost";
    QString dbname = "xxxxxxx";
    QString uname = "admin";
    QString pword = "xxxxxx";
    int port = 3306;
    dbcon.setHostName(host);
    dbcon.setDatabaseName(dbname);
    dbcon.setUserName(uname);
    dbcon.setPassword(pword);
    dbcon.setPort(port);


    if ( !dbcon.open() ) //condition used to check if database connection was bad
    {
        QSqlError error = dbcon.lastError();
        QMessageBox::warning(this,"Error Connecting to database",
                             QString("There was an error while connecting to %1, the error is %2").arg(dbname).arg(error.text()),
                             QMessageBox::Ok,QMessageBox::Cancel);
    }
    else //if connection to database was good write vouchers to database
    {
        QFile theFile(im_FileName); //create a file with the csv which was imported
        int count = 0;
        QString line = QString();

        //check to see if the file can be opened
        if ( !theFile.open(QIODevice::ReadOnly | QIODevice::Text))
        {
            QMessageBox::warning(this,tr("Error Opening the file!"),QString("There was an erro opening file %1").arg(im_FileName),QMessageBox::Ok,QMessageBox::Close);
        }
        else //if the file was opened successfully, create a stream and update the vouchers
        {
            //ensure that a valid filename and voucher time has been specified

            QTextStream stream(&theFile);
            QSqlQuery query(dbcon);

            //loop through the stream reading one line at a time
            while ( !stream.atEnd() )
            {
                count++;
                line = stream.readLine();
                if ( count > 7 && !line.isEmpty() && !line.isNull() ) //make sure the line read has something in it
                {
                    QString newLine( line.remove(QRegExp("[\\s\"]") ) );
                    query.prepare("INSERT INTO vouchers (code, valid_time, isUsed, isPrinted)" "VALUES (?, ?, ?, ?)");
                    //query.prepare("INSERT INTO vouchers (code, valid_time, isUsed, isPrinted)" "VALUES (:code, :time, :used, :printed");
                    query.bindValue(0,newLine);
                    query.bindValue(1,voucherTime);
                    query.bindValue(2,false);
                    query.bindValue(3,false);
                    bool status = query.exec();
                    total++; //for each line of voucher code that is valid, update the total number of lines int the file
                    if ( status == true ) { insertions++; } //if code was entered into database okay, update amount of good insertions
                }
            }//end of while loop

        }//end of file open else

    }//end of database open else
    dbcon.close();
}

QSqlDatabase::removeDatabase("updateconnection");
ui->statusBar->showMessage(QString("%1 of %2 voucher codes were successfully inserted!").arg(insertions).arg(total),5000);

}

What are mistakes I have made?

Thanks in advance.

You want to keep the window responsive during the inserts ? In that case, you can execute your insert function in a thread. — Ilya, Mar 07 '16 at 21:54
Thank you, I started reading up on threading but did not understand much. I did some more digging and came up with a temporary solution which involves using QCoreApplication::processEvents(). What would be the drawbacks of using this function? — Alexander Guerrero, Mar 07 '16 at 22:41
A thread is more flexible, separates things better, and not that hard to implement in your case. Using one would be a good tutorial for the cases where you really need them, otherwise I guess processEvents can do the job here. — Ilya, Mar 07 '16 at 22:47
to execute your queries in a different thread, I wrote [MSqlQuery](https://github.com/micjabbour/msqlquery), maybe give it a try. It gives you a similar interface to `QSqlQuery`, so it shouldn't take you a long time to get started. . . — Mike, Mar 08 '16 at 07:17

score 1 · Accepted Answer · edited May 23 '17 at 10:28

Have you tried moving the query.prepare() line outside of (above) the while loop? It looks like you are preparing the statement for each record. That should be unnecessary as reusability is the reason prepared statements exist.

Also, even after reusing the prepared statement, you are performing over 1,000 operations. It may just be a slow operation. You may want to consider putting your DB code into another thread to keep the UI thread responsive.

I know your example code shows that your server is on localhost. If it was not, I'd re-assert that a separate DB thread would be advisable--many round trips to a server can really add up.

Finally, MySQL has two tools you could employ. One is extended inserts. You could build SQL command strings with the extended insert syntax to insert batches of records in one go (if you do, be sure to handle quoting the values). The second is the LOAD DATA command; which is intended to import data.

Qt program hangs during insertion of 1023 rows of data into mysql database

1 Answers1