1 From e52b57b7a9f0303c0c710e60870d0ec265d32541 Mon Sep 17 00:00:00 2001
2 From: Milian Wolff <mail@milianw.de>
3 Date: Mon, 1 Dec 2014 14:11:19 +0100
4 Subject: [PATCH 19/30] Optimize queries: Do not retrieve known key used in the
7 There is no point in doing a select like:
9 SELECT foo, bar FROM table WHERE foo = needle;
11 That can be rewritten to say
13 SELECT bar FROM table WHERE foo = needle;
15 This reduces the data traffic with the mysql server. Additionally, it
16 work-arounds some issues in Qt SQL, which lead to bad performance:
17 QSqlResult::value incurs multiple temporary allocations, and string
18 conversions, even to read a simple integer ID for example. Finally,
19 by reusing an externally provided QString name e.g., we can leverage
20 Qt's implicit sharing, instead of duplicating the string in a separate
21 QString instance, with the contents read from SQL server.
25 server/src/storage/entities.xsl | 50 +++++++++++++++++++++++++++++------------
26 1 file changed, 36 insertions(+), 14 deletions(-)
28 diff --git a/server/src/storage/entities.xsl b/server/src/storage/entities.xsl
29 index 9471293..c8fb1fd 100644
30 --- a/server/src/storage/entities.xsl
31 +++ b/server/src/storage/entities.xsl
32 @@ -104,6 +104,12 @@ Q_DECLARE_TYPEINFO( Akonadi::Server::<xsl:value-of select="@name"/>, Q_MOVABLE_T
34 using namespace Akonadi::Server;
36 +static QStringList removeEntry(QStringList list, const QString& entry)
38 + list.removeOne(entry);
42 <xsl:for-each select="database/table">
43 <xsl:call-template name="table-source"/>
45 @@ -179,7 +185,8 @@ set<xsl:value-of select="$methodName"/>( <xsl:call-template name="argument"/> )
46 return <xsl:value-of select="$className"/>();
48 QueryBuilder qb( tableName(), QueryBuilder::Select );
49 - qb.addColumns( columnNames() );
50 + static const QStringList columns = removeEntry(columnNames(), <xsl:value-of select="$key"/>Column());
51 + qb.addColumns( columns );
52 qb.addValueCondition( <xsl:value-of select="$key"/>Column(), Query::Equals, <xsl:value-of select="$key"/> );
54 akDebug() << "Error during selection of record with <xsl:value-of select="$key"/>"
55 @@ -191,21 +198,36 @@ set<xsl:value-of select="$methodName"/>( <xsl:call-template name="argument"/> )
56 return <xsl:value-of select="$className"/>();
59 + <!-- this indirection is required to prevent off-by-one access now that we skip the key column -->
61 + <xsl:for-each select="column">
62 + const <xsl:value-of select="@type"/> value<xsl:value-of select="position()"/> =
64 + <xsl:when test="@name=$key">
65 + <xsl:value-of select="$key"/>;
68 + (qb.query().isNull(valueIndex)) ?
69 + <xsl:value-of select="@type"/>() :
71 + <xsl:when test="starts-with(@type,'QString')">
72 + Utils::variantToString( qb.query().value( valueIndex ) )
74 + <xsl:when test="starts-with(@type, 'Tristate')">
75 + static_cast<Tristate>(qb.query().value( valueIndex ).value<int>())
78 + qb.query().value( valueIndex ).value<<xsl:value-of select="@type"/>>()
86 <xsl:value-of select="$className"/> rv(
87 <xsl:for-each select="column">
88 - (qb.query().isNull(<xsl:value-of select="position() - 1"/>)) ?
89 - <xsl:value-of select="@type"/>() :
91 - <xsl:when test="starts-with(@type,'QString')">
92 - Utils::variantToString( qb.query().value( <xsl:value-of select="position() - 1"/> ) )
94 - <xsl:when test="starts-with(@type, 'Tristate')">
95 - static_cast<Tristate>(qb.query().value( <xsl:value-of select="position() - 1"/> ).value<int>())
98 - qb.query().value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>()
101 + value<xsl:value-of select="position()"/>
102 <xsl:if test="position() != last()">,</xsl:if>