Работа с подготовленными запросами (QSqlQuery::prepare) (Firebird 2.1), оптимизация запросов, кэширование, транзакции Опции

[Steklova Olga]

Всем привет
Пишу класс для работы с БД.
Написала метод класса, выполняющий подключение к БД.
Написала метод класса, выполняющий вставку записей в таблицу. В таком виде он работает.

QString DBFunctions::insertToDB()
{
    QString squery;
    QSqlQuery queryIns_T_BLOCK;
    squery = "INSERT INTO T_BLOCK (A, B, C) VALUES (:A, :B, :C)";
    if (!queryIns_T_BLOCK.prepare(squery))
        return "QUERY_PREP_ERR: " + QString("'%1' %2")
          .arg("INSERT INTO T_BLOCK").arg(queryIns_T_BLOCK.lastError().text());

    for (int i = 1; i <= 3; i++) {
        queryIns_T_BLOCK.bindValue(":A", 100 * i + 1); //тестовый вариант
        queryIns_T_BLOCK.bindValue(":B", 100 * i + 2);
        queryIns_T_BLOCK.bindValue(":C", 100 * i + 3);
        queryIns_T_BLOCK.exec();
        if (!queryIns_T_BLOCK.isActive())
            return "QUERY_EXEC_ERR: " + QString("'%1' %2")
              .arg("INSERT INTO T_BLOCK").arg(queryIns_T_BLOCK.lastError().text());
        }         

    return "";
}

Метод insertToDB() в процессе работы программы вызывается многократно.
В результате, каждый раз подготавливается один и тот же запрос.
Как сделать так, чтобы запрос подготавливался только один раз?
Я хотела вынести подготовку запроса в другой метод класса, который можно будет вызвать однократно.
Насколько я понимаю, для этого queryIns_T_BLOCK должно быть известно в обоих этих методах.
Еще не написав доп. метод, а только выполнив перенос описания QSqlQuery queryIns_T_BLOCK сюда

class DBFunctions
{
...
private:
    QSqlQuery queryIns_T_BLOCK;
};

у меня перестала работать подготовка запроса.
prepare стал выдавать ошибку Driver not loaded. Не понимаю, в чем дело.
Экземпляры класса QSqlQuery могут быть только локальными переменными?

Сообщение отредактировал Steklova Olga - 7.2.2014, 18:45

[Litkevich Yuriy]

Да это не страшно, FireBird кэширует подготовленные запросы. (они именно в таком виде и улетают в БД из твоей программы)

у меня перестала работать подготовка запроса. prepare стал выдавать ошибку Driver not loaded. Не понимаю, в чем дело.

по всей видимости это произошло потому, что QSqlQuery будучи членом класса создаётся тогда же когда создаётся экземпляр класса DBFunctions, а он в свою очередь создаётся до соединения с БД. Соответственно этот QSqlQuery пытается работать с недействительным соединением.

В обще его лучше оставить в покое (т.е пусть он будет локальным в функции), заодно и имя ему дать короткое - q.

[Steklova Olga]

Вы правы, Юрий, вот так prepare работает.

код с указателем

class DBFunctions
{
...
private:
    QString squery;
    QSqlQuery *q;
};
QString DBFunctions::prepareQueries()
{
    q = new QSqlQuery();
    squery = "INSERT INTO T_BLOCK (A, B, C) VALUES (:A, :B, :C)";
    if (!q->prepare(squery))
        return "QUERY_PREP_ERR: " + QString("'%1' %2")
          .arg("INSERT INTO T_BLOCK").arg(q->lastError().text());
    return "";
}
QString DBFunctions::insertToDB(const int x)
{
    for (int i = 1; i <= 3; i++) {
        q->bindValue(":A", 100 * i + x); //тестовый вариант
        q->bindValue(":B", 100 * i + x);
        q->bindValue(":C", 100 * i + x);
        q->exec();
        if (!q->isActive())
            return "QUERY_EXEC_ERR: " + QString("'%1' %2")
              .arg("INSERT INTO T_BLOCK").arg(q->lastError().text());
    }
    return "";
}

Прочитала про Кэш_запросов_(СУБД)

Как работает кэширование запросов в Firebird - к сожалению, не нашла. Где бы про это почитать?

Нашла только Кэш запросов в MySQL
Там написано:
"В процессе работы кэш запросов хранит текст запроса SELECT вместе с соответствующим результатом, который посылался клиенту.
При получении другого идентичного запроса сервер извлечет результаты из кэша запросов, а не будет анализировать и выполнять снова тот же самый запрос."
А в Firebird (в отличие от MySQL) отправляются в кэш не запросы SELECT, а все запросы, для которых делаем prepare?
А обычные запросы, выполняемые как q.exec(sQuery), отправляются в кэш?
И новый подготавливаемый запрос заносится в кэш, если в данный момент в кэше нет точно такого же действительного запроса?
и
Как работает кэширование запросов в MySQL
Там написано:
"При изменениях таблицы (INSERT, UPDATE, DELETE, TRUNCATE, ALTER или DROP TABLE|DATABASE),
все кэшированные запросы, использовавшие данную таблицу, становятся недействительными и удаляются из кэша."
То есть, в MySQL при этом становятся недействительными запросы SELECT, связанные с этой таблицей?

Насколько я понимаю, в Firebird уже подготовленный запрос тоже может стать недействительным.
Например, если
1) подготовить запрос на вставку записей в таблицу,
2) подготовить запрос на выборку данных из этой таблицы,
3) если выполнить запрос на вставку, то запрос на выборку, подготовленный заранее, станет недействительным, и хотя он и не выдаст ошибок, но не вернет ни одной записи из таблицы (так у меня получилось при проверке),
4) поэтому для выполнения запроса на выборку (после выполнения запроса на вставку) надо заново подготовить запрос на выборку.
Видимо, действительно, проще будет лишний раз подготовить запрос, чем разбираться, где он может стать недействительным.

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

И еще вопрос про запросы, которые выполняются в программе больше одного раза.
Лучше будет для оптимизации вообще все эти запросы делать с prepare? Или есть случаи, когда лучше их выполнить сразу, без подготовки, как q.exec(sQuery) ?

[Litkevich Yuriy]

Где бы про это почитать?

у Хелен Бори есть

А я и не думала, что надо учитывать такие вещи, мне только хотелось оптимизировать работу, чтобы не загружать БД разборкой одинаковых запросов.

вот собственно и не думай, оптимизирует сама СУБД, обрати внимание на примеры Qt - QSqlQuery всегда локальная переменная.

Применительно к Firebird.
Из Qt в Qt-драйвер БД отправляется срока prepare и пачка (массив) аргументов, этот драйвер прям как есть всё отправляет в Firebird, т.е. строка prepare и массив аргументов.
Firebird по строке prepare строит план запроса и сохраняет его, затем используя массив аргументов сам "биндит" их в план.

В большинстве случаев можно полагать, что сделав с двух разных машин и программ один и тот же prepare Firebird постарается воспользоватся уже подготовленным (с предыдущего события) планом запроса.

Firebird кэширует план запроса, в прочем MySQL тоже должен так делать. Собственно времени больше всего уходит именно на построение плана запроса.

Лучше будет для оптимизации вообще все эти запросы делать с prepare? Или есть случаи, когда лучше их выполнить сразу, без подготовки, как q.exec(sQuery) ?

prepare полезно делать в ситуации, когда ты собираешься несколько наборов данных одинаковым запросом отправлять.

П.С.
Обрамить всю пачку prepare-bind в транзакцию - существенно увеличить скорость выполнения этой пачки (для любой БД), а для Firebird-а ещё и меньше мусорить, т.к. он версии состояний хранит.

П.П.С.
Т.к. Firebird версионник, он работает сильно не так как MySql. Недействительным план запроса может стать в других ситуациях.

В процессе работы кэш запросов хранит текст запроса SELECT вместе с соответствующим результатом, который посылался клиенту.

Вот в Firebird я не помню чтоб результат запроса сколь-нибудь долго кэшировался, для заданной версии, может быть, но версий множество по-этому в общем котле вряд ли. А вот план кэшируется всегда для любого типа запроса

[Steklova Olga]

Чем дальше пишешь, тем больше вопросов Подскажите, пожалуйста, кто знает.
1)

Как работает кэширование запросов в Firebird - к сожалению, не нашла. Где бы про это почитать?

у Хелен Бори есть

У Хелен много написано “про кэш в общем”, еще на стр. 414-417 есть про подготовку запросов, а вот конкретно про кэширование запросов я ничего там не нашла.

2) Вы уже сказали, что подготовленные запросы кэшируются (рассматривались запросы с параметрами).
А какие из запросов q1 и q2 будут кэшироваться?
Если кэшироваться будет только q2, то не лучше ли будет для ускорения работы использовать q2 вместо q1?

    QString squery = "UPDATE T_BLOCK SET A = 0";
    QSqlQuery q1;
    q1.exec(squery);
    QSqlQuery q2;
    q2.prepare(squery);
    q2.exec();

3)

Обрамить всю пачку prepare-bind в транзакцию - существенно увеличить скорость выполнения этой пачки (для любой БД), а для Firebird-а ещё и меньше мусорить, т.к. он версии состояний хранит.

То есть так?

см код

QString DBFunctions::insertToDB()
{
    QString squery;
    int qntRec = 10;

    QSqlDatabase::database().transaction();
    QSqlQuery q;
    squery = "INSERT INTO T_BLOCK (A, B, C) VALUES (:A, :B, :C)";
    if (!q.prepare(squery)) {
        QSqlDatabase::database().rollback();
        return "QUERY_PREP_ERR: " + QString("'%1' %2")
          .arg("INSERT INTO T_BLOCK").arg(q.lastError().text());
    }
    for (int i = 1; i <= qntRec; i++) {
        q.bindValue(":A", i + 1); //тестовый вариант
        q.bindValue(":B", i + 2);
        q.bindValue(":C", i + 3);
        q.exec();
        if (!q.isActive()) {
            QSqlDatabase::database().rollback();
            return "QUERY_EXEC_ERR: " + QString("'%1' %2")
              .arg("INSERT INTO T_BLOCK").arg(q.lastError().text());
        }         
    }         
    QSqlDatabase::database().commit();

    return "";
}

Это стоит сделать независимо из количества выполнений запроса (qntRec)?
И при qntRec = 10, и при qntRec = 20000?
А почему это приводит к увеличению скорости? Где почитать про это?

4) Дополнение к предыдущему пункту.
У Хелен Борри на стр. 417 сказано:
“При помещении в базу данных большого количества данных в пакете предпочтительным является разбиение их на группы и подтверждение работы приблизительно через каждые 5000-10000 строк.”

То есть надо стартовать транзакцию,
объявить запрос, подготовить его,
забиндить значения параметров и выполнить запрос для первых 10000 записей,
закоммитить,
потом опять стартовать транзакцию,
объявить запрос, подготовить его,
забиндить значения параметров и выполнить запрос для следующих 10000 записей,
закоммитить? Так?

5) Прочла сейчас в документации в разделе Executing SQL Statements | Transactions, что
When using transactions you must start the transaction before you create your query.
Раньше не знала, поэтому у меня в программе было примерно такое:

см код

QString squery;
int ID = 5;
QSqlQuery q;
squery = "SELECT FIRST 1 ID FROM T1 WHERE ID = " + QString::number(ID);
q.exec(squery);
int N = (q.next()) ? 10 : 20;
QSqlDatabase::database().transaction();
squery = "DELETE FROM T2 WHERE ID = " + QString::number(ID);
q.exec(squery);
if (!q.isActive()) {
    QSqlDatabase::database().rollback();
    return;
}
if (!q.prepare("INSERT INTO T2 (ID, N, F) VALUES (:ID, :N, :F)")) {
    QSqlDatabase::database().rollback();
    return;
}
q.bindValue(":ID", ID);
q.bindValue(":N", N);
for (int i = 0; i <= 10; i++)
{
    q.bindValue(":F", i);
    q.exec();
    if (!q.isActive()) {
        QSqlDatabase::database().rollback();
        return;
    }
}
QSqlDatabase::database().commit();

То есть у меня было написано
объявить запрос (и использовать это объявление для нескольких запросов),
выполнить неподготовленный запрос,
стартовать транзакцию,
выполнить неподготовленный запрос,
подготовить запрос, забиндить значения параметров и выполнить его,
закоммитить.

Запрос на выборку, как я понимаю, выполнялся в рамках “транзакции по умолчанию”.
Запросы на удаление и вставку должны либо выполниться оба, либо не выполниться совсем.
Получается, что надо

QSqlDatabase::database().transaction();

перенести перед

QSqlQuery q;

А что вытворяла программа в том варианте, как был у меня? Она не выдавала ошибок, но, вероятно, делала что-то не то…

6)

Т.к. Firebird версионник, он работает сильно не так как MySql. Недействительным план запроса может стать в других ситуациях.

В каких? Это надо как-то дополнительно проверять?
Недостаточно просто объявить запрос, подготовить его, проверить результат prepare, забиндить значения параметров запроса и выполнить его? Притом обрамив все это в транзакцию.

7) Тут говорят, что
“Если некий сеанс запустил и закоммитил удаление 100500 записей, по возможности пусть он же выполнит после этого и select count(*) from t where <тот же критерий, что был при удалении этих 100500 записей>. Всё таки правило "мусор убирают те, кто его породил" - лучше, чем "... те, кто на него напоролся". ”
А если у меня в таблице, например, 15000 записей. Надо их почти все удалить. Значит надо делать так? И транзакция здесь тоже нужна?

см код

    QString squery;
    int ID = 10;

    QSqlDatabase::database().transaction();
    QSqlQuery q;
    squery = "DELETE FROM T_BLOCK “
             ”WHERE ID > " + QString::number(ID);
    q.exec(squery);
    if (!q.isActive()) {
        QSqlDatabase::database().rollback();
        return "QUERY_EXEC_ERR: " + QString("'%1' %2")
          .arg("DELETE FROM T_BLOCK").arg(q.lastError().text());
    }
    squery = "SELECT COUNT(*) FROM T_BLOCK “
             ”WHERE ID > " + QString::number(ID);
    q.exec(squery);
    if (!q.isActive()) {
        QSqlDatabase::database().rollback();
        return "QUERY_EXEC_ERR: " + QString("'%1' %2")
          .arg("SELECT COUNT(*) FROM T_BLOCK").arg(q.lastError().text());
    }
    QSqlDatabase::database().commit();

    return "";

8 ) У Жасмин Бланшет на стр. 321 написано, что
“Класс QSqlQuery содержит некоторые другие функции [кроме next()] для просмотра результирующего набора: first(), last(), previous() и seek().
Эти функции удобны, но для некоторых баз данных они могут выполняться медленнее и расходовать памяти больше, чем функция next().
При работе с большими наборами данных мы можем осуществить простую оптимизацию, вызывая функцию QSqlQuery::setForwardOnly(true) перед вызовом exec(), и только затем использовать next() для перемещения по результирующему набору.”

А в доке по QSqlQuery::setForwardOnly написано, что
“For this to be true, you must call setForwardOnly() before the query is prepared or executed.
Note that the constructor that takes a query and a database may execute the query.
Forward only mode is off by default.
Calling setForwardOnly after execution of the query will result in unexpected results at best, and crashes at worst.”

А если я выполняю несколько запросов, используя один QSqlQuery q, то мне каждый раз надо вызывать setForwardOnly?
То есть, так?

см код

QString squery;
int ID = 10;

QSqlDatabase::database().transaction();
QSqlQuery q;
squery = "SELECT * FROM T1";
q.setForwardOnly(true); //перед exec неподготавливаемого запроса
q.exec(squery);
while (q.next()) {
    …
}
squery = "SELECT * FROM T2 WHERE ID > :ID";
q.setForwardOnly(true); //перед prepare подготавливаемого запроса
q.prepare(squery);
q.bindValue(":ID", ID);
q.exec();
while (q.next()) {
    …
}
QSqlDatabase::database().commit();

Буду очень благодарна за помощь

Сообщение отредактировал Steklova Olga - 10.2.2014, 12:14

[Litkevich Yuriy]

То есть так?

на мой вгляд это:

if (!q.prepare(squery))

лишнее условие

А почему это приводит к увеличению скорости?

потому что, когда ты не начинаешь транзакцию явно, то транзакция создаётся не явно, т.е. будет пачка транзакций вместо одной, на каждый "бинд". Это даже на SQLite очень хорошо заметно (для ~50 вставок скорость увеличивается в 2-3 раза)

Это стоит сделать независимо из количества выполнений запроса (qntRec)?И при qntRec = 10, и при qntRec = 20000?

Я обычно оборачиваю в транзакцию, если циклов потенциально может быть больше 5.

“При помещении в базу данных большого количества данных в пакете предпочтительным является разбиение их на группы и подтверждение работы приблизительно через каждые 5000-10000 строк.”

У Firebird-а есть точки сохранения (savepoints) и здесь они имеются ввиду. Просто для надёжности, а вдруг связь с сервером прервётся. Но их не поддерживает оригинальный драйвер Qt. На прог.орге человек выкладывал свой драйвер Firebird-а, вроде он их поддерживает.

объявить запрос (и использовать это объявление для нескольких запросов),

Это всёго лишь экземпляр класса QSqlQuery, но не запрос с точки зрения СУБД.
Нужно лишь такое:

QSqlDatabase::database().transaction(); // Начали транзакцию
q.exec(squery); // выполнили запрос
QSqlDatabase::database().commit();// зафиксировали, или откатились

Для FB откатыватся при ошибках сильно рекомендую, иначе мертвые транзакции так и останутся в БД, т.к. FB хранит версии своих состояний.

Недостаточно просто объявить запрос, подготовить его, проверить результат prepare, забиндить значения параметров запроса и выполнить его? Притом обрамив все это в транзакцию.

достаточно

[Litkevich Yuriy]

q.exec(squery);
    if (!q.isActive())

Если ты хочешь проверить успешность запроса, то смотри что возвращает exec(), а не активность/неактивность. Часто isActive() будет возвращать Ложь

А если я выполняю несколько запросов, используя один QSqlQuery q, то мне каждый раз надо вызывать setForwardOnly?

Нет

А если у меня в таблице, например, 15000 записей. Надо их почти все удалить. Значит надо делать так? И транзакция здесь тоже нужна?

Транзакции лучше применять всегда явно.

Но драйвер Qt для Firebird убогий, в нём нельзя задавать тип транзакции.

[Steklova Olga]

9)

squery = "INSERT INTO T_BLOCK (A, B, C) VALUES (:A, :B, :C)";
if (!q.prepare(squery))...

на мой вгляд это лишнее условие

Обычно я проверяю это условие, чтобы отдельно проверить правильность формирования строки squery. Ведь она не всегда столь тривиальна.

Хотя в доке и сказано, что
bool QSqlQuery::prepare ( const QString & query )
Prepares the SQL query for execution.
Returns true if the query is prepared successfully; otherwise returns false.
Portability note: Some databases choose to delay preparing a query until it is executed the first time.
In this case, preparing a syntactically wrong query succeeds, but every consecutive exec() will fail.

10)

q.exec(squery);
if (!q.isActive())...

Если ты хочешь проверить успешность запроса, то смотри что возвращает exec(), а не активность/неактивность. Часто isActive() будет возвращать Ложь

В доке сказано, что
bool QSqlQuery::exec ( const QString & query )
Executes the SQL in query.
Returns true and sets the query state to active if the query was successful; (isActive == true)
otherwise returns false. (здесь нельзя рассчитывать, что isActive == false?)

bool QSqlQuery::exec ()
Executes a previously prepared SQL query.
Returns true if the query executed successfully; (здесь можно рассчитывать, что isActive == true?)
otherwise returns false. (здесь нельзя рассчитывать, что isActive == false?)

bool QSqlQuery::isActive () const
Returns true if the query is active.
An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.
When you are finished with an active query, you can make the query inactive by calling finish() or clear(), or you can delete the QSqlQuery instance.

А если q.exec(squery) вернет true, то разве q.isActive() может при этом вернуть false?
Я ведь не делаю для запроса finish(), clear() или delete.

[Litkevich Yuriy]

чтобы отдельно проверить правильность формирования строки squery

Some databases choose to delay preparing a query until it is executed the first time

Применительно к Firebird это именно так, т.е. бесполезно.

А если q.exec(squery) вернет true, то разве q.isActive() может при этом вернуть false?

запросто, я вообще отказался от использования isActive() из-за не предсказуемости. Смотрю только результат exec()