PostgreSQL中的快速批量执行

Question

我有大量的数据需要插入数据库，希望能够在最短的时间内完成。我进行了一些测试，在PostgreSQL中创建了一个表（使用以下脚本）：

CREATE TABLE test_table
(
  id serial NOT NULL,
  item integer NOT NULL,
  count integer NOT NULL,
  CONSTRAINT test_table_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_table OWNER TO postgres;

我写了测试代码，生成了1000个随机值，并以两种不同的方式插入到test_table中。第一种方式使用QSqlQuery::exec()

int insert() {
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");

db.setHostName("127.0.0.1");
db.setDatabaseName("TestDB");
db.setUserName("postgres");
db.setPassword("1234");

if (!db.open()) {
    qDebug() << "can not open DB";
    return -1;
}

QString queryString = QString("INSERT INTO test_table (item, count)"
        " VALUES (:item, :count)");

QSqlQuery query;
query.prepare(queryString);

QDateTime start = QDateTime::currentDateTime();

for (int i = 0; i < 1000; i++) {

    query.bindValue(":item", qrand());
    query.bindValue(":count", qrand());

    if (!query.exec()) {
        qDebug() << query.lastQuery();
        qDebug() << query.lastError();
    }

} //end of for i

QDateTime end = QDateTime::currentDateTime();
int diff = start.msecsTo(end);
return diff;
}

第二种方法使用 QSqlQuery::execBatch：

int batchInsert() {
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");

db.setHostName("127.0.0.1");
db.setDatabaseName("TestDB");
db.setUserName("postgres");
db.setPassword("1234");

if (!db.open()) {
    qDebug() << "can not open DB";
    return -1;
}

QString queryString = QString("INSERT INTO test_table (item, count)"
        " VALUES (:item, :count)");

QSqlQuery query;
query.prepare(queryString);

QVariantList itemList;
QVariantList CountList;

QDateTime start = QDateTime::currentDateTime();

for (int i = 0; i < 1000; i++) {

    itemList.append(qrand());
    CountList.append(qrand());

} //end of for i

query.addBindValue(itemList);
query.addBindValue(CountList);

if (!query.execBatch())
    qDebug() << query.lastError();

QDateTime end = QDateTime::currentDateTime();
int diff = start.msecsTo(end);
return diff;
}

我发现它们之间没有任何区别：

int main() {
qDebug() << insert() << batchInsert();
return 1;}

结果：

14270 14663 (milliseconds)

如何改进它？

在http://doc.qt.io/qt-5/qsqlquery.html#execBatch 中引用了：

如果数据库不支持批量执行，则驱动程序将使用常规的 exec() 调用来模拟它。

我不确定我的数据库管理系统是否支持批处理执行？ 我该如何测试它？

- esmaeil mollaahmadi

Answer 1

我不确定qt驱动程序的作用，但PostgreSQL支持在一个事务中运行多个语句。只需手动执行而不是尝试使用驱动程序的内置功能即可。

尝试将您的SQL语句更改为

BEGIN TRANSACTION;

每次循环运行插入语句。

INSERT HERE;

当所有1000条记录循环结束后，请执行此操作。在同一连接上进行。

COMMIT TRANSACTION;

同时，测试1000行数据可能不足以验证，您可能需要尝试使用100,000行或更多行来确保qt批处理确实没有起到帮助作用。

- Kuberchaun

---

请确保您的连接不会自动提交。如果是这样，我的回答目标将会失去，因为它会在每次插入时都提交，而不是等待最终的提交语句。 - Kuberchaun

Answer 2

通过发出1000个插入语句，你需要进行1000个数据库往返。这需要相当长的时间（网络和调度延迟）。因此，尽量减少插入语句的数量！

假设你想要：

insert into test_table(item, count) values (1000, 10);
insert into test_table(item, count) values (1001, 20);
insert into test_table(item, count) values (1002, 30);

将其转换为单个查询，查询所需时间不到一半：

insert into test_table(item, count) values (1000, 10), (1001, 20), (1002, 30);

在PostgreSQL中，有另一种写法：

insert into test_table(item, count) values (
  unnest(array[1000, 1001, 1002])
  unnest(array[10, 20, 30]));

我介绍第二种方法的原因是您可以将大数组的所有内容通过单个参数传递（在使用数据库驱动程序"Npgsql"的C#中进行了测试）：

insert into test_table(item, count) values (unnest(:items), unnest(:counts));

• items是一个查询参数，值为int[]{100, 1001, 1002}
• counts是一个查询参数，值为int[]{10, 20, 30}

今天，我使用这种技术将C#中的10000个插入操作的运行时间从80秒减少到了550毫秒。这很容易实现。此外，由于单个语句永远不会被拆分成多个事务，因此没有任何与事务相关的麻烦。

我希望这种技术在Qt PostgreSQL驱动程序中也能够奏效。在服务器端，您需要使用PostgreSQL >= 8.4版本，因为旧版本不提供unnest函数（但可能会有变通方法）。

- hagello

Answer 3

您可以使用QSqlDriver::hasFeature函数并传入QSqlDriver::BatchOperations参数。

在4.8版本的源代码中，我发现只有oci（oracle）支持BatchOperations。不知道为什么psql驱动程序没有使用COPY语句来支持postgresql。

- serj