Tremendous Speed-Up with SQL Transactions

The function CustomerDatabase::importCustomers reads 500 customers from the list c_customers, creates an SQL query cmd for each customer and inserts each customer into the Customers table of the SQL database db.

void CustomerDatabase::importCustomers() {
    auto db = QSqlDatabase::database("CustomerDB");
    for (auto row : c_customers) {
        auto cmd = QString{"INSERT INTO Customers "
                   "(fullName, street, postalCode, city, phone, email) "
                   "VALUES (\'%1\')"}.arg(row.join("\', \'"));
        auto query = QSqlQuery{cmd, db};
    }
}

On an NXP i.MX6 SoC with four Cortex-A9 cores, this function takes 17.5 seconds! Your user would have to wait more than 17.5 seconds for the import of a CSV file with 500 customers to finish. This is unacceptable. How can you speed up the import to 0.2 seconds?

The solution is as simple as it is powerful. You put the for loop with its 500 SQL queries into one SQL transaction.

void CustomerDatabase::importCustomers() {
    auto db = QSqlDatabase::database("CustomerDB");
    db.transaction();
    for (auto row : c_customers) {
        auto cmd = QString{"INSERT INTO Customers "
                   "(fullName, street, postalCode, city, phone, email) "
                   "VALUES (\'%1\')"}.arg(row.join("\', \'"));
        auto query = QSqlQuery{cmd, db};
    }
    db.commit();
}

The function now takes 0.2 seconds. That’s nearly 90 times faster than without transactions!