]>
Commit | Line | Data |
---|---|---|
8a8f9fb3 AM |
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 | |
5 | condition. | |
6 | ||
7 | There is no point in doing a select like: | |
8 | ||
9 | SELECT foo, bar FROM table WHERE foo = needle; | |
10 | ||
11 | That can be rewritten to say | |
12 | ||
13 | SELECT bar FROM table WHERE foo = needle; | |
14 | ||
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. | |
22 | ||
23 | REVIEW: 121310 | |
24 | --- | |
25 | server/src/storage/entities.xsl | 50 +++++++++++++++++++++++++++++------------ | |
26 | 1 file changed, 36 insertions(+), 14 deletions(-) | |
27 | ||
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 | |
33 | ||
34 | using namespace Akonadi::Server; | |
35 | ||
36 | +static QStringList removeEntry(QStringList list, const QString& entry) | |
37 | +{ | |
38 | + list.removeOne(entry); | |
39 | + return list; | |
40 | +} | |
41 | + | |
42 | <xsl:for-each select="database/table"> | |
43 | <xsl:call-template name="table-source"/> | |
44 | </xsl:for-each> | |
45 | @@ -179,7 +185,8 @@ set<xsl:value-of select="$methodName"/>( <xsl:call-template name="argument"/> ) | |
46 | return <xsl:value-of select="$className"/>(); | |
47 | ||
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"/> ); | |
53 | if ( !qb.exec() ) { | |
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"/>(); | |
57 | } | |
58 | ||
59 | + <!-- this indirection is required to prevent off-by-one access now that we skip the key column --> | |
60 | + int valueIndex = 0; | |
61 | + <xsl:for-each select="column"> | |
62 | + const <xsl:value-of select="@type"/> value<xsl:value-of select="position()"/> = | |
63 | + <xsl:choose> | |
64 | + <xsl:when test="@name=$key"> | |
65 | + <xsl:value-of select="$key"/>; | |
66 | + </xsl:when> | |
67 | + <xsl:otherwise> | |
68 | + (qb.query().isNull(valueIndex)) ? | |
69 | + <xsl:value-of select="@type"/>() : | |
70 | + <xsl:choose> | |
71 | + <xsl:when test="starts-with(@type,'QString')"> | |
72 | + Utils::variantToString( qb.query().value( valueIndex ) ) | |
73 | + </xsl:when> | |
74 | + <xsl:when test="starts-with(@type, 'Tristate')"> | |
75 | + static_cast<Tristate>(qb.query().value( valueIndex ).value<int>()) | |
76 | + </xsl:when> | |
77 | + <xsl:otherwise> | |
78 | + qb.query().value( valueIndex ).value<<xsl:value-of select="@type"/>>() | |
79 | + </xsl:otherwise> | |
80 | + </xsl:choose> | |
81 | + ; ++valueIndex; | |
82 | + </xsl:otherwise> | |
83 | + </xsl:choose> | |
84 | + </xsl:for-each> | |
85 | + | |
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"/>() : | |
90 | - <xsl:choose> | |
91 | - <xsl:when test="starts-with(@type,'QString')"> | |
92 | - Utils::variantToString( qb.query().value( <xsl:value-of select="position() - 1"/> ) ) | |
93 | - </xsl:when> | |
94 | - <xsl:when test="starts-with(@type, 'Tristate')"> | |
95 | - static_cast<Tristate>(qb.query().value( <xsl:value-of select="position() - 1"/> ).value<int>()) | |
96 | - </xsl:when> | |
97 | - <xsl:otherwise> | |
98 | - qb.query().value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>() | |
99 | - </xsl:otherwise> | |
100 | - </xsl:choose> | |
101 | + value<xsl:value-of select="position()"/> | |
102 | <xsl:if test="position() != last()">,</xsl:if> | |
103 | </xsl:for-each> | |
104 | ); | |
105 | -- | |
106 | 2.1.0 | |
107 |