0019-Optimize-queries-Do-not-retrieve-known-key-used-in-t.patch

   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&amp; 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() &lt;&lt; "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&lt;Tristate&gt;(qb.query().value( valueIndex ).value&lt;int&gt;())
  76 +          </xsl:when>
  77 +          <xsl:otherwise>
  78 +          qb.query().value( valueIndex ).value&lt;<xsl:value-of select="@type"/>&gt;()
  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&lt;Tristate&gt;(qb.query().value( <xsl:value-of select="position() - 1"/> ).value&lt;int&gt;())
  96 -        </xsl:when>
  97 -        <xsl:otherwise>
  98 -      qb.query().value( <xsl:value-of select="position() - 1"/> ).value&lt;<xsl:value-of select="@type"/>&gt;()
  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