1 From 63f49d233ca8a4fdd3e8937ea1c80d5e57a1cbdc Mon Sep 17 00:00:00 2001
2 From: Milian Wolff <mail@milianw.de>
3 Date: Tue, 25 Nov 2014 20:16:41 +0100
4 Subject: [PATCH 12/30] Optimize: Reduce the amount of allocations required to
7 The initial implementation of the QueryBuilder was quite naive, when
8 you look at the amount of string allocations it does to build the
9 final query we sent to the SQL server.
11 This was found with Linux perf (no, not even heaptrack!). It
12 showed a huge number of cycles spent in malloc/free, all called
13 eventually by the QueryBuilder.
15 This patch removes most of these allocations. It can further be
16 improved in the future, I bet. Also, the amount of queries we create
17 is pretty large. I guess using stored procedures or something similar
18 might also help the performance. At least, we should try to "remember"
19 some of our queries, and make it possible to reuse them in the
20 functions that run often.
22 The added benchmark shows that the cost is not as big as I'd initially
23 assumed. There are simply many more allocation occurrences in Akonadi
24 currently. Still, I think it's worth it, as it also decreases the
25 memory fragmentation and improves cache locality:
28 RESULT : QueryBuilderTest::benchQueryBuilder():
29 0.0115 msecs per iteration (total: 116, iterations: 10000)
31 113.10MB bytes allocated in total (ignoring deallocations)
32 over 1203089 calls to allocation functions.
33 peak heap memory consumption: 254.46KB
36 RESULT : QueryBuilderTest::benchQueryBuilder():
37 0.0065 msecs per iteration (total: 66, iterations: 10000)
39 62.42MB bytes allocated in total (ignoring deallocations)
40 over 343089 calls to allocation functions.
41 peak heap memory consumption: 254.96KB
43 So before, we had approx. 60 allocations per query build in the
44 benchmark (note that Qt for some reason executes the loop twice,
45 so while the time is measured for 10k iterations, heaptrack will
46 see 20k). With this patch applied, we only need ~20 allocations
47 per query we build up.
49 The remaining allocations are the various append operations to
50 the QList/QVectors mostly, as well as QueryBuilder::addAggregation.
54 server/src/storage/querybuilder.cpp | 210 ++++++++++++++++-------------
55 server/src/storage/querybuilder.h | 14 +-
56 server/tests/unittest/querybuildertest.cpp | 58 ++++++--
57 server/tests/unittest/querybuildertest.h | 2 +
58 4 files changed, 173 insertions(+), 111 deletions(-)
60 diff --git a/server/src/storage/querybuilder.cpp b/server/src/storage/querybuilder.cpp
61 index c079059..3017867 100644
62 --- a/server/src/storage/querybuilder.cpp
63 +++ b/server/src/storage/querybuilder.cpp
66 using namespace Akonadi::Server;
68 -static QString compareOperatorToString( Query::CompareOperator op )
69 +static QLatin1String compareOperatorToString( Query::CompareOperator op )
73 @@ -58,10 +58,10 @@ static QString compareOperatorToString( Query::CompareOperator op )
74 return QLatin1String( " LIKE " );
76 Q_ASSERT_X( false, "QueryBuilder::compareOperatorToString()", "Unknown compare operator." );
78 + return QLatin1String("");
81 -static QString logicOperatorToString( Query::LogicOperator op )
82 +static QLatin1String logicOperatorToString( Query::LogicOperator op )
86 @@ -70,10 +70,10 @@ static QString logicOperatorToString( Query::LogicOperator op )
87 return QLatin1String( " OR " );
89 Q_ASSERT_X( false, "QueryBuilder::logicOperatorToString()", "Unknown logic operator." );
91 + return QLatin1String("");
94 -static QString sortOrderToString( Query::SortOrder order )
95 +static QLatin1String sortOrderToString( Query::SortOrder order )
98 case Query::Ascending:
99 @@ -82,7 +82,17 @@ static QString sortOrderToString( Query::SortOrder order )
100 return QLatin1String( " DESC" );
102 Q_ASSERT_X( false, "QueryBuilder::sortOrderToString()", "Unknown sort order." );
104 + return QLatin1String("");
107 +static void appendJoined( QString *statement, const QStringList &strings, const QLatin1String &glue = QLatin1String( ", " ) )
109 + for (int i = 0, c = strings.size(); i < c; ++i) {
110 + *statement += strings.at( i );
112 + *statement += glue;
117 QueryBuilder::QueryBuilder( const QString &table, QueryBuilder::QueryType type )
118 @@ -94,10 +104,12 @@ QueryBuilder::QueryBuilder( const QString &table, QueryBuilder::QueryType type )
119 , mDatabaseType( DbType::Unknown )
122 - , mIdentificationColumn( QLatin1String( "id" ) )
123 + , mIdentificationColumn( )
127 + static const QString defaultIdColumn = QLatin1String( "id" );
128 + mIdentificationColumn = defaultIdColumn;
131 void QueryBuilder::setDatabaseType( DbType::Type type )
132 @@ -175,60 +187,65 @@ void QueryBuilder::sqliteAdaptUpdateJoin( Query::Condition &condition )
133 qb.addCondition( joinCondition.second );
135 // Convert the subquery to string
136 - condition.mColumn = QLatin1String( "( " ) + qb.buildQuery() + QLatin1String( " )" );
137 + condition.mColumn.reserve(1024);
138 + condition.mColumn.resize(0);
139 + condition.mColumn += QLatin1String( "( " );
140 + qb.buildQuery(&condition.mColumn);
141 + condition.mColumn += QLatin1String( " )" );
145 -QString QueryBuilder::buildQuery()
146 +void QueryBuilder::buildQuery(QString *statement)
150 // we add the ON conditions of Inner Joins in a Update query here
151 // but don't want to change the mRootCondition on each exec().
152 Query::Condition whereCondition = mRootCondition[WhereCondition];
156 - statement += QLatin1String( "SELECT " );
157 + *statement += QLatin1String( "SELECT " );
159 - statement += QLatin1String( "DISTINCT " );
160 + *statement += QLatin1String( "DISTINCT " );
162 Q_ASSERT_X( mColumns.count() > 0, "QueryBuilder::exec()", "No columns specified" );
163 - statement += mColumns.join( QLatin1String( ", " ) );
164 - statement += QLatin1String( " FROM " );
165 - statement += mTable;
166 + appendJoined( statement, mColumns );
167 + *statement += QLatin1String( " FROM " );
168 + *statement += mTable;
169 Q_FOREACH ( const QString &joinedTable, mJoinedTables ) {
170 const QPair<JoinType, Query::Condition> &join = mJoins.value( joinedTable );
171 switch ( join.first ) {
173 - statement += QLatin1String( " LEFT JOIN " );
174 + *statement += QLatin1String( " LEFT JOIN " );
177 - statement += QLatin1String( " INNER JOIN " );
178 + *statement += QLatin1String( " INNER JOIN " );
181 - statement += joinedTable;
182 - statement += QLatin1String( " ON " );
183 - statement += buildWhereCondition( join.second );
184 + *statement += joinedTable;
185 + *statement += QLatin1String( " ON " );
186 + buildWhereCondition( statement, join.second );
191 - statement += QLatin1String( "INSERT INTO " );
192 - statement += mTable;
193 - statement += QLatin1String( " (" );
194 - typedef QPair<QString,QVariant> StringVariantPair;
195 - QStringList cols, vals;
196 - Q_FOREACH ( const StringVariantPair &p, mColumnValues ) {
197 - cols.append( p.first );
198 - vals.append( bindValue( p.second ) );
199 + *statement += QLatin1String( "INSERT INTO " );
200 + *statement += mTable;
201 + *statement += QLatin1String( " (" );
202 + for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
203 + *statement += mColumnValues.at(i).first;
205 + *statement += QLatin1String( ", " );
208 + *statement += QLatin1String( ") VALUES (" );
209 + for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
210 + bindValue( statement, mColumnValues.at(i).second );
212 + *statement += QLatin1String( ", " );
215 - statement += cols.join( QLatin1String( ", " ) );
216 - statement += QLatin1String( ") VALUES (" );
217 - statement += vals.join( QLatin1String( ", " ) );
218 - statement += QLatin1Char( ')' );
219 + *statement += QLatin1Char( ')' );
220 if ( mDatabaseType == DbType::PostgreSQL && !mIdentificationColumn.isEmpty() ) {
221 - statement += QLatin1String( " RETURNING " ) + mIdentificationColumn;
222 + *statement += QLatin1String( " RETURNING " ) + mIdentificationColumn;
226 @@ -246,78 +263,75 @@ QString QueryBuilder::buildQuery()
227 sqliteAdaptUpdateJoin( whereCondition );
230 - statement += QLatin1String( "UPDATE " );
231 - statement += mTable;
232 + *statement += QLatin1String( "UPDATE " );
233 + *statement += mTable;
235 if ( mDatabaseType == DbType::MySQL && !mJoinedTables.isEmpty() ) {
236 // for mysql we list all tables directly
237 - statement += QLatin1String( ", " );
238 - statement += mJoinedTables.join( QLatin1String( ", " ) );
239 + *statement += QLatin1String( ", " );
240 + appendJoined( statement, mJoinedTables );
243 - statement += QLatin1String( " SET " );
244 + *statement += QLatin1String( " SET " );
245 Q_ASSERT_X( mColumnValues.count() >= 1, "QueryBuilder::exec()", "At least one column needs to be changed" );
246 - typedef QPair<QString,QVariant> StringVariantPair;
247 - QStringList updStmts;
248 - Q_FOREACH ( const StringVariantPair &p, mColumnValues ) {
249 - QString updStmt = p.first;
250 - updStmt += QLatin1String( " = " );
251 - updStmt += bindValue( p.second );
252 - updStmts << updStmt;
253 + for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
254 + const QPair<QString, QVariant>& p = mColumnValues.at( i );
255 + *statement += p.first;
256 + *statement += QLatin1String( " = " );
257 + bindValue( statement, p.second );
259 + *statement += QLatin1String( ", " );
262 - statement += updStmts.join( QLatin1String( ", " ) );
264 if ( mDatabaseType == DbType::PostgreSQL && !mJoinedTables.isEmpty() ) {
265 // PSQL have this syntax
266 // FROM t1 JOIN t2 JOIN ...
267 - statement += QLatin1String( " FROM " );
268 - statement += mJoinedTables.join( QLatin1String( " JOIN " ) );
269 + *statement += QLatin1String( " FROM " );
270 + appendJoined( statement, mJoinedTables, QLatin1String( " JOIN " ) );
276 - statement += QLatin1String( "DELETE FROM " );
277 - statement += mTable;
278 + *statement += QLatin1String( "DELETE FROM " );
279 + *statement += mTable;
282 Q_ASSERT_X( false, "QueryBuilder::exec()", "Unknown enum value" );
285 if ( !whereCondition.isEmpty() ) {
286 - statement += QLatin1String( " WHERE " );
287 - statement += buildWhereCondition( whereCondition );
288 + *statement += QLatin1String( " WHERE " );
289 + buildWhereCondition( statement, whereCondition );
292 if ( !mGroupColumns.isEmpty() ) {
293 - statement += QLatin1String( " GROUP BY " );
294 - statement += mGroupColumns.join( QLatin1String( ", " ) );
295 + *statement += QLatin1String( " GROUP BY " );
296 + appendJoined( statement, mGroupColumns );
299 if ( !mRootCondition[HavingCondition].isEmpty() ) {
300 - statement += QLatin1String( " HAVING " );
301 - statement += buildWhereCondition( mRootCondition[HavingCondition] );
302 + *statement += QLatin1String( " HAVING " );
303 + buildWhereCondition( statement, mRootCondition[HavingCondition] );
306 if ( !mSortColumns.isEmpty() ) {
307 Q_ASSERT_X( mType == Select, "QueryBuilder::exec()", "Order statements are only valid for SELECT queries" );
308 - QStringList orderStmts;
309 - typedef QPair<QString, Query::SortOrder> SortColumnInfo;
310 - Q_FOREACH ( const SortColumnInfo &order, mSortColumns ) {
312 - orderStmt += order.first;
313 - orderStmt += sortOrderToString( order.second );
314 - orderStmts << orderStmt;
315 + *statement += QLatin1String( " ORDER BY " );
316 + for (int i = 0, c = mSortColumns.size(); i < c; ++i) {
317 + const QPair<QString, Query::SortOrder>& order = mSortColumns.at( i );
318 + *statement += order.first;
319 + *statement += sortOrderToString( order.second );
321 + *statement += QLatin1String( ", " );
324 - statement += QLatin1String( " ORDER BY " );
325 - statement += orderStmts.join( QLatin1String( ", " ) );
329 - statement += QLatin1Literal( " LIMIT " ) + QString::number( mLimit );
330 + *statement += QLatin1Literal( " LIMIT " ) + QString::number( mLimit );
336 bool QueryBuilder::retryLastTransaction( bool rollback )
337 @@ -334,7 +348,9 @@ bool QueryBuilder::retryLastTransaction( bool rollback )
339 bool QueryBuilder::exec()
341 - const QString statement = buildQuery();
343 + statement.reserve(1024);
344 + buildQuery(&statement);
346 #ifndef QUERYBUILDER_UNITTEST
347 if ( QueryCache::contains( statement ) ) {
348 @@ -443,52 +459,54 @@ void QueryBuilder::addColumn( const QString &col )
350 void QueryBuilder::addAggregation( const QString &col, const QString &aggregate )
352 - QString s( aggregate );
353 - s += QLatin1Char( '(' );
355 - s += QLatin1Char( ')' );
356 - mColumns.append( s );
357 + mColumns.append( aggregate + QLatin1Char( '(' ) + col + QLatin1Char( ')' ) );
360 -QString QueryBuilder::bindValue( const QVariant &value )
361 +void QueryBuilder::bindValue( QString *query, const QVariant &value )
363 mBindValues << value;
364 - return QLatin1Char( ':' ) + QString::number( mBindValues.count() - 1 );
365 + *query += QLatin1Char( ':' ) + QString::number( mBindValues.count() - 1 );
368 -QString QueryBuilder::buildWhereCondition( const Query::Condition &cond )
369 +void QueryBuilder::buildWhereCondition( QString *query, const Query::Condition &cond )
371 if ( !cond.isEmpty() ) {
373 - Q_FOREACH ( const Query::Condition &c, cond.subConditions() ) {
374 - conds << buildWhereCondition( c );
375 + *query += QLatin1String( "( " );
376 + const QLatin1String glue = logicOperatorToString( cond.mCombineOp );
377 + const Query::Condition::List& subConditions = cond.subConditions();
378 + for (int i = 0, c = subConditions.size(); i < c; ++i) {
379 + buildWhereCondition(query, subConditions.at(i));
384 - return QLatin1String( "( " ) + conds.join( logicOperatorToString( cond.mCombineOp ) ) + QLatin1String( " )" );
385 + *query += QLatin1String( " )" );
387 - QString stmt = cond.mColumn;
388 - stmt += compareOperatorToString( cond.mCompareOp );
389 + *query += cond.mColumn;
390 + *query += compareOperatorToString( cond.mCompareOp );
391 if ( cond.mComparedColumn.isEmpty() ) {
392 if ( cond.mComparedValue.isValid() ) {
393 if ( cond.mComparedValue.canConvert( QVariant::List ) ) {
394 - stmt += QLatin1String( "( " );
395 - QStringList entries;
396 - Q_ASSERT_X( !cond.mComparedValue.toList().isEmpty(),
397 + *query += QLatin1String( "( " );
398 + const QVariantList& entries = cond.mComparedValue.toList();
399 + Q_ASSERT_X( !entries.isEmpty(),
400 "QueryBuilder::buildWhereCondition()", "No values given for IN condition." );
401 - Q_FOREACH ( const QVariant &entry, cond.mComparedValue.toList() ) {
402 - entries << bindValue( entry );
403 + for (int i = 0, c = entries.size(); i < c; ++i) {
404 + bindValue( query, entries.at(i) );
406 + *query += QLatin1String( ", " );
409 - stmt += entries.join( QLatin1String( ", " ) );
410 - stmt += QLatin1String( " )" );
411 + *query += QLatin1String( " )" );
413 - stmt += bindValue( cond.mComparedValue );
414 + bindValue( query, cond.mComparedValue );
417 - stmt += QLatin1String( "NULL" );
418 + *query += QLatin1String( "NULL" );
421 - stmt += cond.mComparedColumn;
422 + *query += cond.mComparedColumn;
428 diff --git a/server/src/storage/querybuilder.h b/server/src/storage/querybuilder.h
429 index b380f93..df7c362 100644
430 --- a/server/src/storage/querybuilder.h
431 +++ b/server/src/storage/querybuilder.h
432 @@ -70,7 +70,9 @@ class QueryBuilder
434 /// add condition to HAVING part of the query
435 /// NOTE: only supported for SELECT queries
443 @@ -234,9 +236,9 @@ class QueryBuilder
447 - QString buildQuery();
448 - QString bindValue( const QVariant &value );
449 - QString buildWhereCondition( const Query::Condition &cond );
450 + void buildQuery( QString *query );
451 + void bindValue( QString *query, const QVariant &value );
452 + void buildWhereCondition( QString *query, const Query::Condition &cond );
455 * SQLite does not support JOINs with UPDATE, so we have to convert it into
456 @@ -249,11 +251,11 @@ class QueryBuilder
459 DbType::Type mDatabaseType;
460 - QHash<ConditionType, Query::Condition> mRootCondition;
461 + Query::Condition mRootCondition[NUM_CONDITIONS];
464 QStringList mColumns;
465 - QList<QVariant> mBindValues;
466 + QVector<QVariant> mBindValues;
467 QVector<QPair<QString, Query::SortOrder> > mSortColumns;
468 QStringList mGroupColumns;
469 QVector<QPair<QString, QVariant> > mColumnValues;
470 diff --git a/server/tests/unittest/querybuildertest.cpp b/server/tests/unittest/querybuildertest.cpp
471 index 0aba8a1..92df2a2 100644
472 --- a/server/tests/unittest/querybuildertest.cpp
473 +++ b/server/tests/unittest/querybuildertest.cpp
476 QTEST_MAIN( QueryBuilderTest )
478 +Q_DECLARE_METATYPE(QVector<QVariant>)
480 using namespace Akonadi::Server;
482 void QueryBuilderTest::testQueryBuilder_data()
484 + qRegisterMetaType<QVector<QVariant> >();
486 QTest::addColumn<int>( "qbId" );
487 QTest::addColumn<QString>( "sql" );
488 - QTest::addColumn<QList<QVariant> >( "bindValues" );
489 + QTest::addColumn<QVector<QVariant> >( "bindValues" );
491 QueryBuilder qb( "table", QueryBuilder::Select );
492 qb.addColumn( "col1" );
494 - QTest::newRow( "simple select" ) << mBuilders.count() << QString( "SELECT col1 FROM table" ) << QList<QVariant>();
495 + QTest::newRow( "simple select" ) << mBuilders.count() << QString( "SELECT col1 FROM table" ) << QVector<QVariant>();
497 qb.addColumn( "col2" );
499 - QTest::newRow( "simple select 2" ) << mBuilders.count() << QString( "SELECT col1, col2 FROM table" ) << QList<QVariant>();
500 + QTest::newRow( "simple select 2" ) << mBuilders.count() << QString( "SELECT col1, col2 FROM table" ) << QVector<QVariant>();
502 qb.addValueCondition( "col1", Query::Equals, QVariant( 5 ) );
503 - QList<QVariant> bindVals;
504 + QVector<QVariant> bindVals;
505 bindVals << QVariant( 5 );
507 QTest::newRow( "single where" ) << mBuilders.count() << QString( "SELECT col1, col2 FROM table WHERE ( col1 = :0 )" ) << bindVals;
508 @@ -71,17 +74,17 @@ void QueryBuilderTest::testQueryBuilder_data()
509 qb = QueryBuilder( "table" );
510 qb.addAggregation( "col1", "count" );
512 - QTest::newRow( "single aggregation" ) << mBuilders.count() << QString( "SELECT count(col1) FROM table" ) << QList<QVariant>();
513 + QTest::newRow( "single aggregation" ) << mBuilders.count() << QString( "SELECT count(col1) FROM table" ) << QVector<QVariant>();
515 qb = QueryBuilder( "table" );
516 qb.addColumn( "col1" );
517 qb.addSortColumn( "col1" );
519 - QTest::newRow( "single order by" ) << mBuilders.count() << QString( "SELECT col1 FROM table ORDER BY col1 ASC" ) << QList<QVariant>();
520 + QTest::newRow( "single order by" ) << mBuilders.count() << QString( "SELECT col1 FROM table ORDER BY col1 ASC" ) << QVector<QVariant>();
522 qb.addSortColumn( "col2", Query::Descending );
524 - QTest::newRow( "multiple order by" ) << mBuilders.count() << QString( "SELECT col1 FROM table ORDER BY col1 ASC, col2 DESC" ) << QList<QVariant>();
525 + QTest::newRow( "multiple order by" ) << mBuilders.count() << QString( "SELECT col1 FROM table ORDER BY col1 ASC, col2 DESC" ) << QVector<QVariant>();
527 qb = QueryBuilder( "table" );
528 qb.addColumn( "col1" );
529 @@ -98,7 +101,7 @@ void QueryBuilderTest::testQueryBuilder_data()
530 qb.addColumn( "col1" );
533 - QTest::newRow( "SELECT with LIMIT" ) << mBuilders.count() << QString( "SELECT col1 FROM table LIMIT 1" ) << QList<QVariant>();
534 + QTest::newRow( "SELECT with LIMIT" ) << mBuilders.count() << QString( "SELECT col1 FROM table LIMIT 1" ) << QVector<QVariant>();
536 qb = QueryBuilder( "table", QueryBuilder::Update );
537 qb.setColumnValue( "col1", QString( "bla" ) );
538 @@ -263,7 +266,7 @@ void QueryBuilderTest::testQueryBuilder()
541 QFETCH( QString, sql );
542 - QFETCH( QList<QVariant>, bindValues );
543 + QFETCH( QVector<QVariant>, bindValues );
547 @@ -271,3 +274,40 @@ void QueryBuilderTest::testQueryBuilder()
548 QCOMPARE( mBuilders[qbId].mStatement, sql );
549 QCOMPARE( mBuilders[qbId].mBindValues, bindValues );
552 +void QueryBuilderTest::benchQueryBuilder()
554 + const QString table1 = QLatin1String("Table1");
555 + const QString table2 = QLatin1String("Table2");
556 + const QString table3 = QLatin1String("Table3");
557 + const QString table1_id = QLatin1String("Table1.id");
558 + const QString table2_id = QLatin1String("Table2.id");
559 + const QString table3_id = QLatin1String("Table3.id");
560 + const QString aggregate = QLatin1String("COUNT");
561 + const QVariant value = QVariant::fromValue(QString("asdf"));
563 + const QStringList columns = QStringList()
564 + << QLatin1String("Table1.id")
565 + << QLatin1String("Table1.fooAsdf")
566 + << QLatin1String("Table2.barLala")
567 + << QLatin1String("Table3.xyzFsd");
569 + bool executed = true;
572 + QueryBuilder builder( table1, QueryBuilder::Select );
573 + builder.setDatabaseType( DbType::MySQL );
574 + builder.addColumns( columns );
575 + builder.addJoin( QueryBuilder::InnerJoin, table2, table2_id, table1_id );
576 + builder.addJoin( QueryBuilder::LeftJoin, table3, table1_id, table3_id );
577 + builder.addAggregation( columns.first(), aggregate );
578 + builder.addColumnCondition( columns.at(1), Query::LessOrEqual, columns.last() );
579 + builder.addValueCondition( columns.at(3), Query::Equals, value );
580 + builder.addSortColumn( columns.at(2) );
581 + builder.setLimit( 10 );
582 + builder.addGroupColumn( columns.at(3) );
583 + executed = executed && builder.exec();
588 \ No newline at end of file
589 diff --git a/server/tests/unittest/querybuildertest.h b/server/tests/unittest/querybuildertest.h
590 index 3bb6b22..1bca2cc 100644
591 --- a/server/tests/unittest/querybuildertest.h
592 +++ b/server/tests/unittest/querybuildertest.h
593 @@ -37,6 +37,8 @@ class QueryBuilderTest : public QObject
594 void testQueryBuilder_data();
595 void testQueryBuilder();
597 + void benchQueryBuilder();
600 QList< Akonadi::Server::QueryBuilder > mBuilders;