Быстрая вставка нескольких миллионов строк в 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
 

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