How to insert 1 000 000 rows in MySQL Server 5.5 quickly with Qt



  • I decided to write an algorithm that would be quickly inserted into the base of several hundred million rows in MySQL

    While stopped in to check how many of the same row is inserted into a cycle:

    @

    db = QSqlDatabase::addDatabase("QMYSQL","myFirstConnection");
    db.setHostName("localhost");
    db.setDatabaseName("vova");
    db.setUserName("root");
    db.setPassword("root");
    qDebug() << db.open();
    

    qDebug()<<QTime::currentTime();

    if (query.prepare("INSERT INTO mainpackage (phoneNumberA,phoneNumberB,phoneNumberC,phoneNumberD,phoneNumberE)"
    "VALUES (?,?,?,?,?)"))
    {
    for (int i=0; i<1000000; i++)
    {
    query.addBindValue(QString("1234567891011"));
    query.addBindValue(QString("1234567891012"));
    query.addBindValue(QString("1234567891013"));
    query.addBindValue(QString("1234567891014"));
    query.addBindValue(QString("1234567891015"));

    }

    qDebug() << query.exec() << "blablabla";
    qDebug() << query.lastError().databaseText();
    qDebug()<<QTime::currentTime();
    }

    Instead (?,?,?,?,?) be substituted after a string 1234567891011, etc.

    but when the error falls out: "No data supplied for parameters in prepared statement"

    how to overcome this problem?

    How to apply for high performance query can iterate through 1000 to complete the request (exec)?
    @



  • You do not execute your query one million times, you bind the values one million times and execute your query just once. Besides that your example works fine for me.

    Be aware that you will have to prepare your database before inserting such a large amount of data (setting up apropriate caching, disable indexing and so on).


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.