summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorAndy Shaw <andy.shaw@qt.io>2018-01-08 15:48:01 +0100
committerJani Heikkinen <jani.heikkinen@qt.io>2018-02-08 18:24:48 +0000
commit6c6ace9d23f90845fd424e474d38fe30f070775e (patch)
treedef1c304f95c6ea902448fe66d09f15da5f1e7de
parent41bcb31ab9538328ca05efcb4a01569c9803198c (diff)
psql: Improve performance of record()v5.10.1
In order to save having to always run a query to get the tablename for a known oid then we cache the result on the driver side. The oid stays the same while the table exists, so only on dropping it would it change. Recreating the table causes it to get a new oid, so there is no risk of the old one being associated with the wrong table when this happens, if the driver is still open at that point. The benchmark added shows the improvement from the previous code, before the results for PostgreSQL was: RESULT : tst_QSqlRecord::benchmarkRecord():"0_QPSQL@localhost": 259 msecs per iteration (total: 259, iterations: 1) whereas now it is: RESULT : tst_QSqlRecord::benchmarkRecord():"0_QPSQL@localhost": 0.000014 msecs per iteration (total: 59, iterations: 4194304) Task-number: QTBUG-65226 Change-Id: Ic290cff719102743da84e2044cd23e540f20c96c Reviewed-by: Christian Ehrlicher <ch.ehrlicher@gmx.de> Reviewed-by: Robert Szefner <robertsz27@interia.pl> Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>
-rw-r--r--src/plugins/sqldrivers/psql/qsql_psql.cpp14
-rw-r--r--tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp46
-rw-r--r--tests/benchmarks/sql/kernel/kernel.pro1
-rw-r--r--tests/benchmarks/sql/kernel/qsqlrecord/qsqlrecord.pro5
-rw-r--r--tests/benchmarks/sql/kernel/qsqlrecord/tst_qsqlrecord.cpp191
5 files changed, 252 insertions, 5 deletions
diff --git a/src/plugins/sqldrivers/psql/qsql_psql.cpp b/src/plugins/sqldrivers/psql/qsql_psql.cpp
index 35b0f9a3e3..c381572713 100644
--- a/src/plugins/sqldrivers/psql/qsql_psql.cpp
+++ b/src/plugins/sqldrivers/psql/qsql_psql.cpp
@@ -183,6 +183,7 @@ public:
void setDatestyle();
void setByteaOutput();
void detectBackslashEscape();
+ mutable QHash<int, QString> oidToTable;
};
void QPSQLDriverPrivate::appendTables(QStringList &tl, QSqlQuery &t, QChar type)
@@ -553,11 +554,16 @@ QSqlRecord QPSQLResult::record() const
f.setName(QString::fromUtf8(PQfname(d->result, i)));
else
f.setName(QString::fromLocal8Bit(PQfname(d->result, i)));
- QSqlQuery qry(driver()->createResult());
- if (qry.exec(QStringLiteral("SELECT relname FROM pg_class WHERE pg_class.oid = %1")
- .arg(PQftable(d->result, i))) && qry.next()) {
- f.setTableName(qry.value(0).toString());
+ const int tableOid = PQftable(d->result, i);
+ auto &tableName = d->drv_d_func()->oidToTable[tableOid];
+ if (tableName.isEmpty()) {
+ QSqlQuery qry(driver()->createResult());
+ if (qry.exec(QStringLiteral("SELECT relname FROM pg_class WHERE pg_class.oid = %1")
+ .arg(tableOid)) && qry.next()) {
+ tableName = qry.value(0).toString();
+ }
}
+ f.setTableName(tableName);
int ptype = PQftype(d->result, i);
f.setType(qDecodePSQLType(ptype));
int len = PQfsize(d->result, i);
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 1a0340f153..58b87180c8 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -363,7 +363,8 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db )
<< qTableName("task_234422", __FILE__, db)
<< qTableName("test141895", __FILE__, db)
<< qTableName("qtest_oraOCINumber", __FILE__, db)
- << qTableName("bug2192", __FILE__, db);
+ << qTableName("bug2192", __FILE__, db)
+ << qTableName("tst_record", __FILE__, db);
if (dbType == QSqlDriver::PostgreSQL)
tablenames << qTableName("task_233829", __FILE__, db);
@@ -978,6 +979,29 @@ void tst_QSqlQuery::value()
}
}
+#define SETUP_RECORD_TABLE \
+ do { \
+ QVERIFY_SQL(q, exec("CREATE TABLE " + tst_record + " (id integer, extra varchar(50))")); \
+ for (int i = 0; i < 3; ++i) \
+ QVERIFY_SQL(q, exec(QString("INSERT INTO " + tst_record + " VALUES(%1, 'extra%1')").arg(i))); \
+ } while (0)
+
+#define CHECK_RECORD \
+ do { \
+ QVERIFY_SQL(q, exec(QString("select %1.id, %1.t_varchar, %1.t_char, %2.id, %2.extra from %1, %2 where " \
+ "%1.id = %2.id order by %1.id").arg(lowerQTest).arg(tst_record))); \
+ QCOMPARE(q.record().fieldName(0).toLower(), QString("id")); \
+ QCOMPARE(q.record().field(0).tableName().toLower(), lowerQTest); \
+ QCOMPARE(q.record().fieldName(1).toLower(), QString("t_varchar")); \
+ QCOMPARE(q.record().field(1).tableName().toLower(), lowerQTest); \
+ QCOMPARE(q.record().fieldName(2).toLower(), QString("t_char")); \
+ QCOMPARE(q.record().field(2).tableName().toLower(), lowerQTest); \
+ QCOMPARE(q.record().fieldName(3).toLower(), QString("id")); \
+ QCOMPARE(q.record().field(3).tableName().toLower(), tst_record); \
+ QCOMPARE(q.record().fieldName(4).toLower(), QString("extra")); \
+ QCOMPARE(q.record().field(4).tableName().toLower(), tst_record); \
+ } while (0)
+
void tst_QSqlQuery::record()
{
QFETCH( QString, dbName );
@@ -999,6 +1023,26 @@ void tst_QSqlQuery::record()
QCOMPARE( q.record().fieldName( 0 ).toLower(), QString( "id" ) );
QCOMPARE( q.value( 0 ).toInt(), 2 );
+
+ const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+ if (dbType == QSqlDriver::Oracle)
+ QSKIP("Getting the tablename is not supported in Oracle");
+ const auto lowerQTest = qtest.toLower();
+ for (int i = 0; i < 3; ++i)
+ QCOMPARE(q.record().field(i).tableName().toLower(), lowerQTest);
+ q.clear();
+ const auto tst_record = qTableName("tst_record", __FILE__, db).toLower();
+ SETUP_RECORD_TABLE;
+ CHECK_RECORD;
+ q.clear();
+
+ // Recreate the tables, in a different order
+ const QStringList tables = { qtest, tst_record, qTableName("qtest_null", __FILE__, db) };
+ tst_Databases::safeDropTables(db, tables);
+ SETUP_RECORD_TABLE;
+ createTestTables(db);
+ populateTestTables(db);
+ CHECK_RECORD;
}
void tst_QSqlQuery::isValid()
diff --git a/tests/benchmarks/sql/kernel/kernel.pro b/tests/benchmarks/sql/kernel/kernel.pro
index f907feeeac..63887daf5f 100644
--- a/tests/benchmarks/sql/kernel/kernel.pro
+++ b/tests/benchmarks/sql/kernel/kernel.pro
@@ -1,3 +1,4 @@
TEMPLATE = subdirs
SUBDIRS = \
qsqlquery \
+ qsqlrecord
diff --git a/tests/benchmarks/sql/kernel/qsqlrecord/qsqlrecord.pro b/tests/benchmarks/sql/kernel/qsqlrecord/qsqlrecord.pro
new file mode 100644
index 0000000000..840a11bfbe
--- /dev/null
+++ b/tests/benchmarks/sql/kernel/qsqlrecord/qsqlrecord.pro
@@ -0,0 +1,5 @@
+TARGET = tst_bench_qsqlrecord
+
+SOURCES += tst_qsqlrecord.cpp
+
+QT = core sql testlib core-private sql-private
diff --git a/tests/benchmarks/sql/kernel/qsqlrecord/tst_qsqlrecord.cpp b/tests/benchmarks/sql/kernel/qsqlrecord/tst_qsqlrecord.cpp
new file mode 100644
index 0000000000..465dabca0e
--- /dev/null
+++ b/tests/benchmarks/sql/kernel/qsqlrecord/tst_qsqlrecord.cpp
@@ -0,0 +1,191 @@
+/****************************************************************************
+ **
+ ** Copyright (C) 2018 The Qt Company Ltd.
+ ** Contact: https://www.qt.io/licensing/
+ **
+ ** This file is part of the test suite of the Qt Toolkit.
+ **
+ ** $QT_BEGIN_LICENSE:GPL-EXCEPT$
+ ** Commercial License Usage
+ ** Licensees holding valid commercial Qt licenses may use this file in
+ ** accordance with the commercial license agreement provided with the
+ ** Software or, alternatively, in accordance with the terms contained in
+ ** a written agreement between you and The Qt Company. For licensing terms
+ ** and conditions see https://www.qt.io/terms-conditions. For further
+ ** information use the contact form at https://www.qt.io/contact-us.
+ **
+ ** GNU General Public License Usage
+ ** Alternatively, this file may be used under the terms of the GNU
+ ** General Public License version 3 as published by the Free Software
+ ** Foundation with exceptions as appearing in the file LICENSE.GPL3-EXCEPT
+ ** included in the packaging of this file. Please review the following
+ ** information to ensure the GNU General Public License requirements will
+ ** be met: https://www.gnu.org/licenses/gpl-3.0.html.
+ **
+ ** $QT_END_LICENSE$
+ **
+ ****************************************************************************/
+
+#include <QtTest/QtTest>
+#include <QtSql/QtSql>
+
+#include "../../../../auto/sql/kernel/qsqldatabase/tst_databases.h"
+
+const QString qtest(qTableName("qtest", __FILE__, QSqlDatabase()));
+
+class tst_QSqlRecord : public QObject
+{
+ Q_OBJECT
+
+public:
+ tst_QSqlRecord();
+ virtual ~tst_QSqlRecord();
+
+public slots:
+ void initTestCase();
+ void cleanupTestCase();
+ void init();
+ void cleanup();
+
+private slots:
+ void benchmarkRecord_data() { generic_data(); }
+ void benchmarkRecord();
+
+private:
+ void generic_data(const QString &engine = QString());
+ void dropTestTables(QSqlDatabase db);
+ void createTestTables(QSqlDatabase db);
+ void populateTestTables(QSqlDatabase db);
+
+ tst_Databases dbs;
+};
+
+QTEST_MAIN(tst_QSqlRecord)
+
+tst_QSqlRecord::tst_QSqlRecord()
+{
+}
+
+tst_QSqlRecord::~tst_QSqlRecord()
+{
+}
+
+void tst_QSqlRecord::initTestCase()
+{
+ dbs.open();
+ for (const auto &dbName : qAsConst(dbs.dbNames)) {
+ QSqlDatabase db = QSqlDatabase::database(dbName);
+ CHECK_DATABASE(db);
+ dropTestTables(db); // In case of leftovers
+ createTestTables(db);
+ populateTestTables(db);
+ }
+}
+
+void tst_QSqlRecord::cleanupTestCase()
+{
+ for (const auto &dbName : qAsConst(dbs.dbNames)) {
+ QSqlDatabase db = QSqlDatabase::database(dbName);
+ CHECK_DATABASE(db);
+ dropTestTables(db);
+ }
+ dbs.close();
+}
+
+void tst_QSqlRecord::init()
+{
+}
+
+void tst_QSqlRecord::cleanup()
+{
+ QFETCH(QString, dbName);
+ QSqlDatabase db = QSqlDatabase::database(dbName);
+ CHECK_DATABASE(db);
+ const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+
+ if (QTest::currentTestFailed() && (dbType == QSqlDriver::Oracle ||
+ db.driverName().startsWith("QODBC"))) {
+ // Since Oracle ODBC has a problem when encountering an error, we init again
+ db.close();
+ db.open();
+ }
+}
+
+void tst_QSqlRecord::generic_data(const QString &engine)
+{
+ if (dbs.fillTestTable(engine) == 0) {
+ if (engine.isEmpty())
+ QSKIP("No database drivers are available in this Qt configuration");
+ else
+ QSKIP(QString("No database drivers of type %1 are available in this Qt configuration").arg(engine).toLocal8Bit());
+ }
+}
+
+void tst_QSqlRecord::dropTestTables(QSqlDatabase db)
+{
+ QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+ QStringList tablenames;
+ // drop all the tables in case a testcase failed
+ tablenames << qtest
+ << qTableName("record", __FILE__, db);
+ tst_Databases::safeDropTables(db, tablenames);
+
+ if (dbType == QSqlDriver::Oracle) {
+ QSqlQuery q(db);
+ q.exec("DROP PACKAGE " + qTableName("pkg", __FILE__, db));
+ }
+}
+
+void tst_QSqlRecord::createTestTables(QSqlDatabase db)
+{
+ QSqlQuery q(db);
+ switch (tst_Databases::getDatabaseType(db)) {
+ case QSqlDriver::PostgreSQL:
+ QVERIFY_SQL(q, exec("set client_min_messages='warning'"));
+ QVERIFY_SQL(q, exec("create table " + qtest + " (id serial NOT NULL, t_varchar varchar(20), "
+ "t_char char(20), primary key(id)) WITH OIDS"));
+ break;
+ case QSqlDriver::MySqlServer:
+ QVERIFY_SQL(q, exec("set table_type=innodb"));
+ Q_FALLTHROUGH();
+ default:
+ QVERIFY_SQL(q, exec("create table " + qtest + " (id int " + tst_Databases::autoFieldName(db) +
+ " NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id))"));
+ break;
+ }
+}
+
+void tst_QSqlRecord::populateTestTables(QSqlDatabase db)
+{
+ QSqlQuery q(db);
+ QVERIFY_SQL(q, exec("delete from " + qtest));
+ QVERIFY_SQL(q, exec("insert into " + qtest + " values (1, 'VarChar1', 'Char1')"));
+ QVERIFY_SQL(q, exec("insert into " + qtest + " values (2, 'VarChar2', 'Char2')"));
+ QVERIFY_SQL(q, exec("insert into " + qtest + " values (3, 'VarChar3', 'Char3')"));
+ QVERIFY_SQL(q, exec("insert into " + qtest + " values (4, 'VarChar4', 'Char4')"));
+ QVERIFY_SQL(q, exec("insert into " + qtest + " values (5, 'VarChar5', 'Char5')"));
+}
+
+void tst_QSqlRecord::benchmarkRecord()
+{
+ QFETCH(QString, dbName);
+ QSqlDatabase db = QSqlDatabase::database(dbName);
+ CHECK_DATABASE(db);
+ const auto tableName = qTableName("record", __FILE__, db);
+ {
+ QSqlQuery qry(db);
+ QVERIFY_SQL(qry, exec("create table " + tableName + " (id int NOT NULL, t_varchar varchar(20), "
+ "t_char char(20), primary key(id))"));
+ for (int i = 0; i < 1000; i++)
+ QVERIFY_SQL(qry, exec(QString("INSERT INTO " + tableName +
+ " VALUES (%1, 'VarChar%1', 'Char%1')").arg(i)));
+ QVERIFY_SQL(qry, exec(QString("SELECT * from ") + tableName));
+ QBENCHMARK {
+ while (qry.next())
+ qry.record();
+ }
+ }
+ tst_Databases::safeDropTables(db, QStringList() << tableName);
+}
+
+#include "tst_qsqlrecord.moc"