Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Быстрая вставка нескольких миллионов строк в MySQL с помощью QT



  • Здравствуйте!

    Решил написать алгоритм, который бы быстро вставлял в базу несколько сотен миллионов строк в MySQL

    Пока остановился на том, чтобы проверить как вставляется несколько одинаковых строк в цикле:

    @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&#40;&#41; << "blablabla";
        qDebug(&#41; << query.lastError(&#41;.databaseText(&#41;;
        qDebug()<<QTime::currentTime();
    }
    

    @

    Вместо (?,?,?,?,?) должны ведь подставляться строки 1234567891011 и т. д.

    однако при выполнении вываливается ошибка : "No data supplied for parameters in prepared statement"

    1. Как побороть данную проблему?

    2. И как подавать на выполнение большой запрос, может через 1000 итерацию выполнять запрос (exec)?

    [Edit: Please not forget @-tags for code /Vass]



  • Здравствуйте. В следующий раз делайте правильное форматирование поста, пожалуйста :)

    Количество вызовов addBindValue() должно чётко соответствовать количеству плейсхолдеров ("?" в данном случае) в prepare().



  • Спасибо!
    А как тогда сделать мне вставку 1 млн строк, когда я хочу подставлять вместо ? реальные значения через переменные с помощью prepare,exec?



  • Также MySQL (как минимум, версии 5.5) поддерживает подготовку и выполнение запроса следующего вида:
    @"INSERT INTO table (a,b,c) VALUES (?,?,?),(?,?,?),(?,?,?)"@
    Разумеется, повторений скобок может быть больше.
    Проверьте следующий код:
    @
    const int N = 1000000;
    QString queryString = "INSERT INTO mainpackage (phoneNumberA,phoneNumberB,phoneNumberC,phoneNumberD,phoneNumberE) VALUES ”;
    queryString.append( QString( "(?,?,?,?,?)," ).repeated( N ) );
    queryString.chop( 1 );
    if( !query.prepare( queryString ) )
    {
    qDebug() << "Unable to prepare query:" << query.lastError();
    return;
    }
    for( int i = 0; i < N; ++i )
    {
    query.addBindValue( "value_A" );
    query.addBindValue( "value_B" );
    query.addBindValue( "value_C" );
    query.addBindValue( "value_D" );
    query.addBindValue( "value_E" );
    }
    if( !query.exec() )
    {
    qDebug() << "Unable to exec query:" << query.lastError();
    return;
    }@



  • Кстати, при таком количестве вставляемых строк следует следить за памятью. Сама строка запроса будет занимать уже минимум 25МБ (не знаю, сколько точно займёт в памяти тот QString с вопросами). При слишком большом расходе памяти надо будет разбить запрос на более мелкие.
    Разумеется, строку запроса создавать снова при этом не надо, но подготовить запрос нужно.



  • ага, спасибо, учту, буду экспериментировать, в файде my.ini прописаны ограничения на буфер и от этого тоже буду отталкиваться



  • Работает!

    Я хочу пойти по похожему пути (и бэкап тоже попробую потом сделать , но сейчас стоит другая задача):
    http://www.qtcentre.org/threads/12759-Multiple-database-connections

    А вобще задача стоит такая:
    Каждый день формируется бинарный файл с частотой в 3 часа ( дописываются байты с телефонной станции)
    ЗА день получается около 2млн записей и файл занимает около 150 МБ.
    История хранится 3 года в итоге получаем (!) 2 млр записей, которые надо вставить в MySQL Server 5.5.15.

    Мне надо написать свой конвертер, который будет читать каждый байт, сохранять во временные переменные, содержимое которых и будет подставляться под этот код:

    @
    QString queryString = "INSERT INTO mainpackage (phoneNumberA,phoneNumberB,phoneNumberC,phoneNumberD,phoneNumberE) VALUES ”;
    queryString.append( QString( "(?,?,?,?,?)," ).repeated( N ) );
    queryString.chop( 1 );
    @

    Наверное придется в несколько потоков это выполнять, 1 поток читает, а второй вставляет ( когда N достигло константы допустим 1000 )

    Вобще такая операция очень трудоемкая, может еще что нибудь подскажите как действовать в такой ситуации?)



  • Самый быстрый способ - работа с файлом БД напрямую, без выполнения запросов.
    Например, вот так: http://habrahabr.ru/blogs/mysql/126612/



  • Пожалуйста не надо дублировать топики на двух языках, если Вы хотите спросить у всего сообщества и в достаточной мере владеете языком, спрашивайте в общем форуме.

    Здесь, либо если Вас интересует только локальное мнение русскоговорящего сообщества, либо вы не владеете английским языком.

    Дубль темы здесь: http://developer.qt.nokia.com/forums/viewthread/9822/



  • Доброго времени суток.
    Т.к это маленький вопрос, то отдельную тему создавать не имеет большого смысла, а эта вполне подходит. В доках перечислены драйвера к разным БД. Но что-то не понимаю, с ними уже можно работать, или нужно еще подключиться к исходникам?



  • По-умолчанию установлен только драйвер для SQLite, остальные нужно скомпилировать самому.



  • Спасибо :)



  • Как очистить подготовленный SQL запрос от команд BindValue, чтобы при последней вставке в базу не попали дублирующиеся записи?

    Допустим в предпоследней итерации я занес 1000 значений, в последней итерации набралось меньше 1000, получается, в базу попадут старые записи от связывания в предыдущей итерации

    Как освободить QSqlquery чтобы в последней итерации попало допустим ровно 855 записей, а не 1000 записей с 145 старыми записями от предыдущего связывания?

    Пробовал так:
    @
    queryForInsertInBigTable.boundValues().values().clear();
    @
    В результате импорт останавливатся :(



  • Здравствуйте, спустя полтора года. :)

    Вам же не только значений нужно меньше забиндить, но и саму строку запроса поменять, чтобы количество placeholder'ов было нужным.
    Судя по исходникам QSqlQuery из Qt 5.0, при выполнении QSqlQuery::prepare() старое содержимое запроса стирается полностью. То есть, если возникла ситуация, когда записать нужно меньше 1000, то нужно составить новую строку запроса, вызвать QSqlQuery::prepare() с ней и снова вызвать QSqlQuery::bindValue() необходимое число раз с необходимыми данными.
    Если данные хранятся только в самом QSqlQuery, то нужно создать новый запрос и скопировать в него данные.

    То, что вы пробовали, не может работать, так как QSqlQuery::boundValues() возвращает rvalue.



  • "Если данные хранятся только в самом QSqlQuery, то нужно создать новый запрос и скопировать в него данные.
    То, что вы пробовали, не может работать, так как QSqlQuery::boundValues() возвращает rvalue."

    Да,p-himik, данные хранятся только в самом QSqlQuery.
    Создавал новый запрос, но вот данные со старого QSqlQuery для вставки в новый не могу считать.
    Пришлось запускать в debug режиме, смотреть что не так, и что самое интересное:

    я не могу считать уже связанные данные, использовал 2 варианта с официальной документации:
    http://qt-project.org/doc/qt-4.8/qsqlquery.html#boundValues

    И только я хочу считать через

    @
    QMapIterator<QString, QVariant> i(query.boundValues());
    @

    или

    @
    QList<QVariant> list = query.boundValues().values();
    @

    как прекращает выполняться программа в debug режиме, не могу следующую команду выполнить во 2 примере или просто выбрасывается ошибка в 1 примере ( новый QSqlquery создавал и в 1 и во 2 м случае через new ).

    обьект старого QSqlquery у меня один на весь проект.

    Запускал на Qt 2010.05

    Можете у себя проверить, если вдруг получится - приведите пожалуйста рабочий код



  • "не могу следующую команду выполнить" и "просто выбрасывается ошибка" - эталон того, как отвечать нельзя. Что значит "не могу" и какая ошибка?
    Вы QSqlQuery::prepare вызываете каждый раз перед серией QSqlQuery::bindValue или только один раз? Надо каждый, иначе забинденные значения будут не заменяться, а добавляться.

    Доступа к MySQL у меня сейчас нет, проверил на SQLite, всё работает. Но вряд ли это можно считать примером, так как это весьма разные БД.



  • QSqlQuery::prepare вызываю 1 раз вот так как здесь:

    @
    const int N = 1000000;
    QString queryString = "INSERT INTO mainpackage (phoneNumberA,phoneNumberB,phoneNumberC,phoneNumberD,phoneNumberE) VALUES ”;
    queryString.append( QString( "(?,?,?,?,?)," ).repeated( N ) );
    queryString.chop( 1 );
    if( !query.prepare( queryString ) )
    {
    qDebug() << "Unable to prepare query:" << query.lastError();
    return;
    }
    @

    а потом только:

    @
    query.addBindValue( "value_A" );
    query.addBindValue( "value_B" );
    query.addBindValue( "value_C" );
    query.addBindValue( "value_D" );
    query.addBindValue( "value_E" );
    @

    Получается после каждого выполнения:

    @
    if( !query.exec() )
    {
    qDebug() << "Unable to exec query:" << query.lastError();
    return;
    }
    @

    надо вызывать:

    @
    if( !query.prepare( queryString ) )
    {
    qDebug() << "Unable to prepare query:" << query.lastError();
    return;
    }
    @

    Покажите пожалуйста как Вы с SQLite брали данные с QSQlquery для формирования и выполнения пакетной вставки при N_current<N_const ( в случае, когда мы можем узнать количество N уже в конце связывания , то есть не зная заранее сколько будет N в конечном счёте)



  • SQLite не умеет вставлять несколько строк, поэтому я и сказал, что БД разные и пример смысла приводить нет.
    Может, вы напишите минимальный неработающий пример, а я попробую объяснить, что в нём не так?



  • Проблема вот в чем:
    http://www.prog.org.ru/topic_14962_0.html

    Из-за этого некоторые участки кода просто не выполняются, а в debug режиме просто перестает выполняться работающий код.
    Завтра попробую на QT 5.0.2 запустить



  • Тоже такое случалось. Попробуйте пересобирать проект в таких случаях. Если не помогает, то имеет смысл попробовать с Visual Studio.

    Но вы описали проблему, в которой не получается отладку произвести. А неработающий код-то будет?



  • В пошаговой отладке программа повисает на следующих командах, взятых с официальной документации :

    @
    QList<QVariant> list = queryForInsertInBigTable.boundValues().values();
    @

    @
    QMapIterator<QString, QVariant> i(queryForInsertInBigTable.boundValues());
    @

    Здесь,
    http://www.qtforum.org/article/14727/getting-values-from-my-database-qt4.html?s=ce4e0742b8d2af67d676768072f65339be6aa007#post60355

    если читать последний комментарий, написали:
    "This only works with stored procedures that modify their parameters."

    Получается, надо прибегать к следующему коду:
    @

              bool isRecord = query.first();  
    
               if (isRecord) 
               {
                   While (query.next()  && i<coutCorrentPreparedRecords )
                   {
                       // брать данные до const - колич-во связанных записей
                   }
               }
    

    @



  • Возможно, в отладке она подвисает из-за того, что в этих инструкциях выполняется "в фоне" достаточно много кода. GDB вообще печален в плане производительности.

    По ссылке автор комментария говорит про получение доступа к результату SELECT-запроса через boundValues().

    Последний отрывок кода, который вы привели, как раз и получает результат, а не забинденные значения.



  • Полистал документацию и наткнулся на забытый мной QSqlQuery::execBatch(). Выглядит так, будто подходит вам идеально - заранее знать количество вставляемых строк там не нужно.



  • Написал код с вашим подходом и выяснил, почему он не будет работать - boundValues() в случае с использованием неименованных плейсхолдеров Qt сам их именует в таком виде: ":f", ":fb", ":fc", ..., ":fp", ":fab", ":fbb", ":fcb", ..., ":fpb", ":fac",.... Похоже, сделано так потому, что на момент генерации имени плейсхолдера неизвестно количество забинденных значений.
    Соответственно, при последующей итерации по boundValues() порядок будет уже не тот.
    Выход - использовать execBatch(), про которую я написал в предыдущем посте или генерировать свои имена для плейсхолдеров. Разумеется, первый подход лучше.


Log in to reply