Работа с подготовленными запросами (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

[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